4

Today I was reading some of my database notes that were taken long time ago. And I saw this query:

UPDATE Sales.Orders
SET (shipcountry, shipregion, shipcity)=
(SELECT country, region, city
FROM Sales.Customers AS C
WHERE C.custid =Sales.Orders.custid)
WHERE custid IN
(SELECT custid FROM Sales.Customers WHERE country = 'USA');

I'm really unfamiliar with this syntax. But my note says it's "valid SQL Server update syntax". Is this true? SSMS can't parse it. When I tried to run it, I got this:

enter image description here

Also SQL Server's UPDATE documentation seems suggest that ( is not allowed after the SET keyword. Anyway, I just want to confirm.

0

1 Answer 1

8

The syntax:

...
SET (shipcountry, shipregion, shipcity) =
    ( SELECT country, region, city 
      ...
    )
...

is valid SQL syntax but not valid SQL Server syntax. It has not yet been implemented in any version of it.

The (expression1, expression2, ...) is called Row constructor and it can be used in various places, e.g. in WHERE / WHEN / HAVING conditions, in UPDATE .. SET (as your question), in VALUES (...) syntax (often used in INSERT statements). In SQL Server it is allowed only in the third case (VALUES), implemented in version 2008.

Other DBMSs (Postgres, Oracle, DB2) have implemented this syntax, fully or partially.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.