3

Just stumbled upon this stackoverflow question: https://stackoverflow.com/questions/13648898/sql-update-with-row-number. The second answer struck me as odd:

UPDATE x
SET x.CODE_DEST = x.New_CODE_DEST
FROM (
      SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST
      FROM DESTINATAIRE_TEMP
      ) x

Here, a column CODE_DEST is updated with the value of ROW_NUMBER(). However, x.CODE_DEST itself is the result of a select query. So, I'm updating the result of a select query. Is this common practice?

4

It's not common but it can work. Under certain circumstances (and I'm not sure of the rules) you can update a column from a CTE or subquery. My guess is that it's very similar to being able to update a view and probably has the same rules.

The times I've done it it's quite a bit faster than joining back to the original table and updating it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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