Update Table from Specific Select Statement SQL

Question

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.

Amit Kumar Singh · Accepted Answer · 2018-06-20 03:53:12Z

2

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

answered Jun 20, 2018 at 3:53

Amit Kumar Singh

4,4952 gold badges11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-06-20 11:08:54Z

1

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.

answered Jun 20, 2018 at 11:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Update Table from Specific Select Statement SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related