debute debute - 3 months ago 10
SQL Question

Set values of all rows with the value of selected row

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.

Answer
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