Set values of all rows with the value of selected row

Question

So I have table like this:

+----+-------+-----+
| id | name  | ... |
+----+-------+-----+
| 1  | test1 | ... |
| 2  | test2 | ... |
| 3  | test3 | ... |
| 4  | test4 | ... |
+----+-------+-----+

What I want is that all of them will have same name, for example test2, it depends on the id. I don't want solution, where I manually write that value. So when I entered number 2, it will change all row's name to the test2, because id = 2 is test2.

I tried this command:

UPDATE table SET name = t.name SELECT t.* FROM table AS t WHERE id = 2;

My expecting solution would be:

+----+-------+-----+
| id | name  | ... |
+----+-------+-----+
| 1  | test2 | ... |
| 2  | test2 | ... |
| 3  | test2 | ... |
| 4  | test2 | ... |
+----+-------+-----+

PS: my table haven't got that name, also structure is completely different, but I have used this jsut for the example.

provide us with an sql fiddle, this way it's much easier for people to test their possible solutions — Jester
– Jester, Commented Aug 30, 2016 at 13:30

Charles Bretana · Accepted Answer · 2016-08-30 13:44:23Z

3

Update table SET name =
   (Select name 
    from table 
    where id = 2)

if this does not work in MySQL, then try this

Update t SET name =
   (Select name 
    from table 
    where id = 2)
From table t

Correct syntax for MariaDB as discovered by OP:

UPDATE table as t, 
     (SELECT name FROM table 
      WHERE id = 2) as temp
  SET t.name = temp.name

edited Aug 30, 2016 at 13:44

answered Aug 30, 2016 at 13:14

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

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

5 Comments

Patrik Krehák Over a year ago

#1093 - Table 'table' is specified twice, both as a target for 'UPDATE' and as a separate source for data

Charles Bretana Over a year ago

Yes.. -------------- So? This is perfectly fine. Does it not work in MySQL ?

Patrik Krehák Over a year ago

Ooh, sorry, I made a mistake :) I don't have MySQL, but MariaDB. I have MySQL on the another computer. It seems like that ALIAS is not working in the MariaDB. But it works in the SELECT.

Charles Bretana Over a year ago

Sorry, I am not familiar with MariaDB, can't help you with syntax for that product, especially if its not standard SQL.

Patrik Krehák Over a year ago

Well, I have found a solution :) So the command for the MariaDB would be like this (also you can enter it into your answer, so it will be more visible): UPDATE table as t, (SELECT name FROM table WHERE id = 2) as temp SET t.name = temp.name

Rick James · Accepted Answer · 2016-08-30 18:43:57Z

1

No better, but perhaps less cryptic:

SELECT @thename := name  FROM table  WHERE id = 2;
UPDATE table  SET name = @thename;

answered Aug 30, 2016 at 18:43

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Set values of all rows with the value of selected row

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Collectives™ on Stack Overflow

Set values of all rows with the value of selected row

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related