Talk:Update (SQL)

Maybe the article does not reflect typical syntax from real databases. For example, this is valid in Microsoft SQL server:

UPDATE authors
SET state = 'ZZ' 
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id

More info here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_82n9.asp

This syntax would need quite a bit of explanation. In my view, it is not intuitive at all. What makes more sense is this:

UPDATE ( SELECT * FROM authors WHERE ... ) AS t1
SET    state = 'ZZ'

Here it is clear that the query determines the rows to be updated. SQL Server's syntax is not clear in this respect. I would propose to add such highly product-specific things here: Comparison of SQL syntax --Stolze 18:52, 6 November 2006 (UTC)[reply]

The problem with ignoring the MS and Sybase syntax is for complex update queries having to manually correlate both the set and where clause of the query quickly becomes a maintenance and readability hazard.

I agree that correlating both is not so easy. But I fail to see how the above MS-specific example helps in this regard. Reading this statement gives: Update the table "authors", set the values in column "state" to 'ZZ', from ...how to fit this into the sentence???..., and finally filter the rows on some predicate in the where clause. --Stolze 01:15, 22 April 2007 (UTC)[reply]