The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
×26

question asked: 01 Aug '13, 14:25

question was seen: 621 times

last updated: 01 Aug '13, 15:02