Multiple UPDATE's in a single query

Question

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.

See : http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — PaulF, Jul 15 '15 at 10:58

score 1 · Accepted Answer · answered Jul 15 '15 at 10:57

1

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;

answered Jul 15 '15 at 10:57

Gordon Linoff

1,242,037
58
646
786

This is a much simpler and easier aproach than what I was thinking. Works really well. Cheers! – Comum Jul 15 '15 at 11:06

Multiple UPDATE's in a single query

1 Answers1