I need to know if I can do this in an UPDATE statement:
UPDATE users SET ('field1', 'field2', 'field3') VALUES ('value1', 'value2', 'value3');
Or similar syntax. I’m using SQLite.
Note:
Nobody understands me, I just want to know if it is possible to SET separate fields to separate values. That’s all.
Advertisement
Answer
There is a (standard SQL) syntax that is similar to what you propose but as far as I know, only Postgres has implemented it:
UPDATE users SET (field1, field2, field3) = ('value1', 'value2', 'value3') WHERE some_condition ;
Tested (for the infidels) in: SQL-Fiddle
This also works in Postgres:
UPDATE users AS u SET (field1, field2, field3) = (f1, f2, f3) FROM ( VALUES ('value1', 'value2', 'value3') ) AS x (f1, f2, f3) WHERE condition ;
This works in Postgres and SQL-Server:
UPDATE users SET field1 = f1, field2 = f2, field3 = f3 FROM ( VALUES ('value1', 'value2', 'value3') ) AS x (f1, f2, f3) WHERE condition ;
and as @JackDouglas commented, this works in Oracle:
UPDATE users SET (field1, field2, field3) = ( SELECT 'value1', 'value2', 'value3' FROM dual ) WHERE condition ;