The ‘s

sql-standard

I just know a little sql basics and need to write a plain sql syntax highlighter/checker
So I'm fighting through the standard…
I stumbled over somthing I'm not sure I understand it right, as I never have seen someone using a SET in that way.

Its definition looks like this:

<multiple column assignment> ::=
<set target list> <equals operator> <assigned row>

Where set target list is defined this way:

<set target list> ::=
<left paren> <set target> [ { <comma> <set target> }... ] <right paren>

This sounds to me as one could do:

UPDATE ...
SET (A, B, C) = (1, 2, 3)

To update A's value to 1, B's to 2 and C's to 3.

What me makes ruminative is the = as I never have seen a SET in this way and can't find any examples in the web using it that way.

So I'm also not sure about understanding the whole definition right.

Could some one tell me is this correct? And if not so, could you explain me what this definition defines otherwise?

Best Answer

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)
Related Question