0

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.

Patrik Krehák
  • 2,595
  • 8
  • 32
  • 62
  • provide us with an sql fiddle, this way it's much easier for people to test their possible solutions – Jester Aug 30 '16 at 13:30

2 Answers2

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
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • #1093 - Table 'table' is specified twice, both as a target for 'UPDATE' and as a separate source for data – Patrik Krehák Aug 30 '16 at 13:17
  • Yes.. -------------- So? This is perfectly fine. Does it not work in MySQL ? – Charles Bretana Aug 30 '16 at 13:23
  • 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. – Patrik Krehák Aug 30 '16 at 13:33
  • Sorry, I am not familiar with MariaDB, can't help you with syntax for that product, especially if its not standard SQL. – Charles Bretana Aug 30 '16 at 13:35
  • 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` – Patrik Krehák Aug 30 '16 at 13:39
1

No better, but perhaps less cryptic:

SELECT @thename := name  FROM table  WHERE id = 2;
UPDATE table  SET name = @thename;
Rick James
  • 135,179
  • 13
  • 127
  • 222