how to copy an entry in a row to another row, but in the same column in mysql

Question

this might sound like a trivial question but i've looked everywhere. I have a table like this:

id   var1
1
2    19353

there is no entry for var1 where id =1 and I want to copy the entry for var1 from the other row to that location so that i have

id   var1
1    19353
2    19353

i've tried but was unsuccessful with:

update table set var1 = (select var1 from table where id=2) where id=1;

any advice? thanks!

Why was this unsuccessful. Looks good to me! What does the table look like after you issue the `UPDATE`? — Andrew Lazarus, Jun 20 '13 at 17:02
i got the error **ERROR 1093 (HY000): You can't specify target table 'table' for update in FROM clause** — user1637359, Jun 20 '13 at 17:04
Ah! Try `update table set var1 = (select t2.var1 from table AS t2 where t2.id=2) where id=1;` — Andrew Lazarus, Jun 20 '13 at 17:05
yup, the column i'm trying to apply it to is of type varchar(255) but this shouldn't really matter right? i did the alias it gave the same error unfortunately — user1637359, Jun 20 '13 at 17:11
By the way, are you really using the table name `table`? **DON'T!** If you are, it needs to be escaped all over the place, or it will confuse the parser. I tested this on Postgres with a table called `t`; works. — Andrew Lazarus, Jun 20 '13 at 17:12
no, i was just trying to simplify the question, but it's not called table - i did use the alias properly but it didn't work — user1637359, Jun 20 '13 at 17:13
i was just looking at this: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause, does that mean i would need a workaround? — user1637359, Jun 20 '13 at 17:16

score 0 · Accepted Answer · answered Jun 20 '13 at 17:16

0

MySQL seems to have its own ideas about the syntax for multi-table update (a/k/a UPDATE FROM). Looks like the following is required. Except, I don't have MySQL to test on.

UPDATE t AS t1, t AS t2
SET t1.var1 = t2.var1
WHERE t1.id=1 AND t2.id=2;

EDIT: I have changed table name to t; we don't want a reserved word there.

answered Jun 20 '13 at 17:16

Andrew Lazarus

18,205
3
35
53

where do i specify the table name? – user1637359 Jun 20 '13 at 17:18
oh in the place where you put t? – user1637359 Jun 20 '13 at 17:19

how to copy an entry in a row to another row, but in the same column in mysql

1 Answers1