CODEWITHSUNDEEP
mysql
Comum
Comum

Reputation: 453

Multiple UPDATE's in a single query

In a database of mine, there is a couple of indexes that got updated and to save a year worth of data I need to rearange these indexes.

So, I have a table called FloatTableOld that has a field that needs to be updated called TagIndex.

I also have the old indexes that are stored in TagTableOld and the new ones are stored in TagTable, that share the field TagName, and I need to replace TagTableOld.TagIndex with TagTable.TagIndex.

I'm having a bit of trouble because I want to do this in a single query. What I've got is:

UPDATE `FloatTableOld` 
SET `FloatTableOld`.`TagIndex` = 
(
    SELECT `relacao`.`newTag` FROM
    (
        SELECT  `TagTable`.`TagName`,
            `TagTableOld`.`TagIndex` AS `oldTag`,
            `TagTable`.`TagIndex` AS `newTag`
        FROM `TagTable`
        INNER JOIN `TagTableOld`
        ON `TagTable`.`TagName` = `TagTableOld`.`TagName`
    ) AS `relacao`
    WHERE `FloatTableOld`.`TagName` = `relacao`.`oldTag`
)
WHERE `FloatTableOld`.`TagIndex` = 
(
    SELECT `FloatTableOld`.`TagIndex` 
    FROM `FloatTableOld`
)

However I get the following error:

ERROR 1093 (HY000): You can't specify target table 'FloatTableOld' for update in FROM clause

Can anyone help me with this? Can't really understand the error.

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You would use update with join for this. If I understand correctly:

update floattableold fto join
       tagtableold tto
       on fto.tagindex = tto.tagindex join
       tagtable tt
       on tt.tagname = tto.tagname
    set fto.tagindex = tt.tagindex;

Upvotes: 1

Related Questions