UPDATE ...
SET (a, b, c) = (1, 2, 3) ;
Yes, your understanding is correct and this is perfectly legal SQL syntax. As @a_horse_with_no_name mentioned in the comments, you can also use it in conditions (WHERE, HAVING, CASE WHEN, ...).
Examples:
WHERE (a,b,c) = (1, 2, 3)
WHERE (a,b,c) >= (1, 2, 3)
WHERE (a,b,c) IN ((1, 2, 3), (1, 1, 1), (2, 2, 2), (4, 5, 6))
WHERE (a,b,c) BETWEEN (1, 2, 3) AND (4, 5, 6)
The inequality (including BETWEEN) conditions use the lexicographic ordering, so a value of (3, 0, 17) would result to true for the above 2nd and 4th examples.
It can also be used for swapping the values from 2 columns:
UPDATE ...
SET (a, b) = (b, a) ;
Not all databases have implemented this syntax however. As far as I know, only in PostgreSQL, all these work.
In MySQL, it works with =, >=, <=, <> and IN but neither with BETWEEN nor in an assignment (like UPDATE).
In Oracle none of the variations work but one can use this, if the subquery returns one row:
SET (a, b, c) = (SELECT expression_a, expression_b, expression_b FROM ...)
so for a simple update, it can be:
SET (a, b, c) = (SELECT 1, 2, 3 FROM dual)
Best Answer
In general terms, a clause is just a logical chunk of a SQL statement - and, usually, is a chunk that is (theoretically) optional.
I'm a SQL Server guy, so I'll be thinking in those terms.
SELECT GETDATE()is a valid SQL ServerSELECTstatement. It'll return the current date and time.If I add a
FROMclause:I'll get back n rows (where n is the number of rows in the
sys.objectstable), each of which will have the same (unnamed) column, showing the current date and time.You can add a
WHEREclause to limit the number of rows you bring back; aGROUP BYclause to either deduplicate your data, or to allow you to put aggregate values in theSELECTlist; a HAVING clause (usually to eliminate rows based on aggregate data); anORDER BYclause to sort the data - but, none of those have to be there.You'll note I said "
SELECTlist" when referring to the list of columns returned. I've never heard this referred to as a clause - presumably, because it does have to be there.An operator, on the other hand is used to combine or compare two complete items. Just like the
+operator lets you combine numbers (1 + 2) or strings ([firstname] + ' ' + [lastname]), theUNION,EXCEPT,DIFFERENCE, andINTERSECToperators let you combine two SQL statements.I'm probably oversimplifying, but thinking in these terms should at least head you in the right direction.