SQL-Server Update SET from SQL Where select is using a field from the table to update

Question

There is nothing wrong with the syntax but I am not getting the right value of ParentCategoryId. How can I get it?

UPDATE Category
SET    ParentCategoryId = (
           SELECT c2.id
           FROM   Category AS c2
           WHERE  c2.OldId = ParentCategoryId -- << how can I get this value
       )
WHERE  OldId IS NOT NULL

question from:https://stackoverflow.com/questions/66051717/sql-server-update-set-from-sql-where-select-is-using-a-field-from-the-table-to-u

Answer 1

You need to give proper two-part column references.

As it stands, the DB has no idea that the ParentCategoryId in the sub-query refers to the outer column.

UPDATE c
SET    ParentCategoryId = (
           SELECT c2.id
           FROM   Category AS c2
           WHERE  c2.OldId = c.ParentCategoryId
       )
FROM Category AS c
WHERE c.OldId IS NOT NULL;

You can also do this as a joined update:

UPDATE c
SET    ParentCategoryId = c2.id
FROM Category AS c
JOIN Category AS c2 ON c2.OldId = c.ParentCategoryId;
-- WHERE c.OldId IS NOT NULL; -- not necessary as now joined