CODEWITHSUNDEEP
sqlsql-serverdatabase
hattenn
hattenn

Reputation: 4399

Updating each row with some values of another row

I would like to write an update query in SQL like the following:

UPDATE A
SET A.PARENT_ID = [ID of another row]
FROM MY_TABLE AS A

What I want to do is, for each row that this update will be applied, I want it to calculate the part [ID of another row] from a query like the following:

SELECT A.NAME, B.NAME, MAX(B.ID)
FROM MY_TABLE A, MY_TABLE B
WHERE A.NAME = B.NAME

So basically:

  1. When it is updating a row, it will try to find the other rows that has the same name as the current row being updated.
  2. Amongst them, it will try to get the row that has the highest ID.
  3. And then it will update the PARENT_ID value of the current row being updated, to the ID value of the row it finds.

What is a way to do something like that in SQL Server 2008?

Upvotes: 1

Views: 131

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

So, every row with the same Name should have the same ParentID, and that should be the highest assigned ID value for that name?

This should do:

;with Highest as (
    select ID,Name,ParentID,MAX(ID) OVER (PARTITION BY Name) as NewParentID
    from MY_TABLE
)
update Highest set ParentID = NewParentID

Further reading:

Upvotes: 3

Michael Harmon
Michael Harmon

Reputation: 746

How about?:

UPDATE A
SET A.PARENT_ID = (SELECT MAX(B.ID)
           FROM MY_TABLE B
           WHERE A.NAME = B.NAME)
FROM MY_TABLE AS A

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try INNER JOIN UPDATE:

UPDATE A
SET A.PARENT_ID = B.mx
FROM MY_TABLE A
INNER JOIN (SELECT C.PARENT_ID mx, MAX(C.ID) OVER (PARTITION BY C.NAME)
            FROM MY_TABLE C
)B 
ON A.PARENT_ID=B.PARENT_ID

provided PARENT_ID is unique.

Upvotes: 2

Related Questions