CODEWITHSUNDEEP
mysql
Bushwacka
Bushwacka

Reputation: 915

Update table in MySQL with subquery

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

Upvotes: 1

Views: 68

Answers (3)

synapze
synapze

Reputation: 251

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'

Upvotes: 0

Mihai
Mihai

Reputation: 26784

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)

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562260

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'

Upvotes: 3

Related Questions