CODEWITHSUNDEEP
sqlsql-server-2008insert
Isuru
Isuru

Reputation: 31323

Clone record in the same table

This answer comes close to what I need but my situation is slightly different. I have a SQL table look like this.

+------+----------+-----------+
|  aid |  afname  |  alname   |
+------+----------+-----------+
|  01  |  John    |  Doe      |
|  02  |  Stephen |  Williams |
|  03  |  Jane    |  Doe      |
|  04  |  Peter   |  Brown    |
|  05  |          |           |
+------+----------+-----------+

Now I need to clone/copy only the afname and alname fields from the row which has the 02 aid and insert it into the row with the aid 05.

This is the query I tried but it throws an Syntax error.

INSERT INTO table1(afname, alname) 
WHERE aid = '05'
SELECT afname, alname
FROM table1
WHERE aid = '02'

Can anyone please tell me a way to accomplish this?

Many thanks.

Upvotes: 2

Views: 1070

Answers (1)

Grisha Weintraub
Grisha Weintraub

Reputation: 7996

If you don't have a row with aid = '05' :

INSERT INTO table1(aid,afname, alname)
SELECT '05', afname, alname
FROM table1
WHERE aid = '02'

If you have a row with aid = '05' and you want to update it :

update t2
set t2.afname = t1.afname, t2.alname = t1.alname
from table1 t1 join table1 t2 on t1.aid = '02' and t2.aid = '05'

Upvotes: 4

Related Questions