MYSQL update with nested subquery

Question

I have 3 tables in this format:

Table Child:

id|parent_id|grandparent_id
1  1         null
2  2         null
3  3         null

Table Parent:

id|grandparent_id
1          1
2          1
3          2

Table GrandParent:

id
1          
2

I need to run a query that updates grandparent_id column in Child table based on the grandparent_id in Parent table. So the correct final form of Child table will be: Table Child:

id|parent_id|grandparent_id
1  1         1
2  2         1
3  3         2

This is the query I have at the moment but it returns more than 1 row which is wrong:

update child set grandparent_id = (
  select gpd.id from GrandParent gp, Parent p 
  where p.grandparent_id = gp.id) 
where 1

Sebastian Brosch · Accepted Answer · 2017-04-27 17:33:05Z

1

You can use the following query to get the UPDATE:

UPDATE Child SET Child.grandparent_id = (
    SELECT GrandParent.id 
    FROM GrandParent INNER JOIN Parent ON GrandParent.id = Parent.grandparent_id
    WHERE Parent.id = parent_id
) WHERE Child.grandparent_id IS NULL;

Demo: http://sqlfiddle.com/#!9/894e97/1/0 (modified table content to show the UPDATE is working).

Hint: Your "correct" example is wrong: GrandParent of Parent with id = 2 is 1!

edited Apr 27, 2017 at 17:33

answered Apr 27, 2017 at 17:15

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

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

2 Comments

Hirad Roshandel Over a year ago

Thanks it worked. So instead of normal join I had to Inner join? and why can't I just say where 1 to run it on everything even when it's not null?

Sebastian Brosch Over a year ago

If you want to UPDATE all Childs you don't need the WHERE part and you can remove it (no need for WHERE 1). I would choose the INNER JOIN to join the tables.

anto_0412 · Accepted Answer · 2017-04-27 17:23:17Z

0

Please give a try on the below query:

Update child c,parent p
set c.grandparent_id = p.grandparent_id
where c.id = parent.id 
and c.grandparent_id = ' '

answered Apr 27, 2017 at 17:23

anto_0412

11 bronze badge

Collectives™ on Stack Overflow

MYSQL update with nested subquery

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related