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 : stackoverflow.com/questions/45494/…

PaulF
– PaulF

2015-07-15 10:58:36 +00:00
Commented Jul 15, 2015 at 10:58 — PaulF
– PaulF, Commented Jul 15, 2015 at 10:58

Gordon Linoff · Accepted Answer · 2015-07-15 10:57:29Z

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, 2015 at 10:57

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Comum Over a year ago

This is a much simpler and easier aproach than what I was thinking. Works really well. Cheers!

Collectives™ on Stack Overflow

Multiple UPDATE's in a single query

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related