Update based on query returning multple values

[Former Member]

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>;

[former_SQLA_member1694878]

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

[Former Member]

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>