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?
asked 01 Aug '13, 14:25
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,
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>