Update table in MySQL with subquery

Question

I would like to update my table with WHERE clause equals to result in my Subquery as you can see in the query below. The result after executing of query should be that the row with name Robert will have value to 1

CREATE TABLE `table1`(
  `name` varchar(30),
  `surname` varchar(30),
  `nextname` varchar(30),
  `value` bit(1)
);



INSERT INTO `table1` 
VALUES
  ('Daniel', 'Hanks', 'Robert', 0),
  ('Robert', 'Pitt', 'Angelina', 0),
  ('Angelina', 'Jolie', 'Monica', 0),
  ('Monica', 'Red', null, 0);



UPDATE `table1` SET `value` = 1 
WHERE `name` IN (SELECT `nextname` FROM `table1`
WHERE `name` = 'Daniel')¨

Thanks

Ryan · Accepted Answer · 2013-12-05 23:43:50Z

3

You can't UPDATE and SELECT from the same table in one query. But MySQL does support UPDATE with JOIN syntax:

UPDATE table1 AS t1
INNER JOIN table1 AS t2 ON t1.name = t2.nextname
SET t1.value = 1 
WHERE t2.name = 'Daniel'

edited Dec 5, 2013 at 23:43

Ryan

28.4k10 gold badges58 silver badges83 bronze badges

answered Dec 5, 2013 at 23:42

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

Comments

Mihai · Accepted Answer · 2013-12-05 23:47:39Z

1

You can also do it with an ugly trick:

UPDATE `table1` SET `value` = 1 
WHERE `name` IN (SELECT `nextname` FROM(SELECT `nextname` FROM `table1`
WHERE `name` = 'Daniel')x)

answered Dec 5, 2013 at 23:47

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

1 Comment

Bushwacka Over a year ago

Thanks. I tried this but the version with JOIN is better to understand to me :)

synapze · Accepted Answer · 2013-12-05 23:54:00Z

0

Your syntax for using a subquery in the WHERE clause is correct. However what are you trying to do exactly? You can achieve the same result without the subquery.

UPDATE `table1` SET `value` = 1 WHERE `name` = 'Daniel'

answered Dec 5, 2013 at 23:54

synapze

2511 silver badge8 bronze badges

2 Comments

Bushwacka Over a year ago

Yes, but it was only for example, so the reason why i wrote my query was that i wanted to show what i meant.

Bill Karwin Over a year ago

This does not achieve the same result. It doesn't update the row for 'Robert'.

Collectives™ on Stack Overflow

Update table in MySQL with subquery

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related