0

I've been trying to do things like this forever, and it just occurred to me to try using the following syntax (using tuples)

update dstTable T
set (T.field1, T.field2, T.field3) = 
       (select S.value1, S.value2, S.value3
        from srcTable S
         where S.key = T.Key);

And surprisingly it worked. I'm using Oracle (12c I think). Is this Oracle specific syntax or is it standard? Does it work on SQL Server? I haven't used SQL Server for years, but my shop will soon be moving off Oracle.

I suppose I could spin up a SQL Service instance and test it myself...

NB: In my example I'm updating the entire table (filling new columns). The update has no where clause so all rows will be updated. Your fields will be set to NULL when the subquery doesn't return a row. (and it must not return more than one row).

Darrel Lee
  • 2,372
  • 22
  • 22
  • 2
    Or even use DBFiddle... – Dale K Feb 10 '22 at 01:53
  • Thanks. Never used it before (I was only vaguely aware of it existence). – Darrel Lee Feb 10 '22 at 02:02
  • 1
    It's Oracle-specific syntax. SQL Server doesn't support tuple-like syntax. The SQL Server equivalent would be `UPDATE SET FROM`: https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server or a `MERGE` statement. – Dai Feb 10 '22 at 02:04

0 Answers0