CODEWITHSUNDEEP
sql-server
Eyal
Eyal

Reputation: 4763

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

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

Upvotes: 0

Views: 39

Answers (2)

Charlieface
Charlieface

Reputation: 71952

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

Upvotes: 1

under
under

Reputation: 3067

If I understood your intention correctly, there is no need for a sub-query, you can just have a simple UPDATE statement:

UPDATE Category
SET    ParentCategoryId = id
WHERE  OldId = ParentCategoryId
AND OldId IS NOT NULL

Upvotes: 1

Related Questions