In Oracle I have the ability to update multiple columns at a single time using a select statement returning all of the values. Does Sybase have an equivalent statement?

UPDATE <table_name>
SET (<column_name>,<column_name>) = ( SELECT (<column_name>,<column_name>) FROM <table_name> WHERE <where_statement>) WHERE <where_statement>;

asked 01 Aug '13, 14:25

judy's gravatar image

judy
46224
accept rate: 0%


Judy,

The statement you provided in Oracle syntax:

UPDATE Table2
SET (ColX,ColY) = ( SELECT (ColA,ColB) FROM Table1 WHERE ColA=1) WHERE ColX=4;

Could be executed on SQL Anywhere using the following SQL statement:

UPDATE Table2
SET ColX=ColA, ColY=ColB
FROM (SELECT ColA,ColB FROM Table1 WHERE ColA=1) as Temp
WHERE ColX=4;

Table1 contains columns 'ColA' and 'ColB' and Table2 contains columns 'ColX' and 'ColY'.

This statement would update rows in Table2 with a ColX = 4, setting ColX to ColA and ColY to ColB where ColA and ColB are cells in a tuple of Table1 that satisfies 'ColA=1'.

Hope this Helps,

Mikel Rychliski

permanent link

answered 01 Aug '13, 14:56

Mikel%20Rychliski's gravatar image

Mikel Rychliski
2.1k1641
accept rate: 30%

edited 01 Aug '13, 15:01

In SQL Anywhere you can use the MERGE statement to do this (and much more).

MERGE INTO <table_name1> AS dest
  USING ( SELECT * FROM <table_name2> WHERE <where_statement> ) AS source
   ON dest.<column_name1> = source.<column_name1> 
  AND dest.<column_name2> = source.<column_name2>
WHEN MATCHED THEN 
   UPDATE SET dest.<column_name3> = source.<column_name3>, 
              dest.<column_name4> = source.<column_name4>
permanent link

answered 01 Aug '13, 15:01

David%20DeHaan's gravatar image

David DeHaan
476610
accept rate: 42%

edited 01 Aug '13, 15:02

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×31

question asked: 01 Aug '13, 14:25

question was seen: 693 times

last updated: 01 Aug '13, 15:02