CODEWITHSUNDEEP
sqlt-sqlsql-server-2014
Haminteu
Haminteu

Reputation: 1334

Update Table from Specific Select Statement SQL

I have the following table:

IDs     ZoneID     ChildID     ParentID
--------------------------------------------
null    1          36000       36000
null    1          36000       36000
null    2          37000       37000

I want to update the IDs column with the following select statement:

SELECT a.ZONEID FROM Table1 a INNER JOIN Table1 b on a.ParentID = b.ChildID

So, the IDs column will be filled with the result of the select statement above.

The result should be:

IDs     ZoneID     ChildID     ParentID
--------------------------------------------
1       1          36000       36000
1       1          36000       36000
2       2          37000       37000

I tried the following tsql:

Update Table1
set IDs = (SELECT a.ZONEID FROM Table1 a INNER JOIN Table1 b on a.ParentID = b.ChildID)

But error appears when I execute it. Too many argument result, something like that.

Please advice.
Thank you.

Upvotes: 0

Views: 31

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use a correlated subquery:

Update Table1
    set IDs = (SELECT a.ZONEID FROM Table1 a WHERE a.ParentID = Table1.ChildID);

You don't need a JOIN in the subquery.

Upvotes: 1

Amit Kumar Singh
Amit Kumar Singh

Reputation: 4475

You can use join with a UPDATE query

Update Table1
set IDs = a.ZONEID 
FROM Table1 a INNER JOIN Table1 b on a.ParentID = b.ChildID

Upvotes: 2

Related Questions