CODEWITHSUNDEEP
sqlmysql
Honey55
Honey55

Reputation: 425

Update data in an existing row taken from another row in the same table

I am trying this query built from findings in various guides and other stackoverflow questions:

UPDATE `characters`
SET `level`, `xp`, `taximask` = (SELECT `level`, `xp`, `taximask`
FROM `characters`
WHERE `guid` = 111) WHERE `name` = 'targetname';

In this example i want to copy the contents of the level, xp and taximask columns from the row where the guid matches 111 to the row where name matches targetname. Both in the same db/table.

Since the 3 columns are just placeholders for a query where i need to copy 25+ columns, i am looking for a way to make the query work and possibly shorten it.

Upvotes: 0

Views: 791

Answers (1)

Honey55
Honey55

Reputation: 425

Here is the working solution i got from a friend:

UPDATE `characters` AS t1
INNER JOIN `characters` AS t2 ON t2.guid = 111
SET t1.level = t2.level, t1.xp = t2.xp, t1.taximask = t2.taximask
WHERE t1.`name` = 'targetname';

Upvotes: 2

Related Questions