Clever Geek Handbook

Update to table concatenated into a composite key

I am trying to update rows in a data table that intersect rows in a smaller index table. The two tables are merged into a composite PK of the data table, and the selection explanation using the same criteria shows that the index is being used correctly and the correct unique rows are fetched, but I am still having problems updating.

Updating the concatenated tables works great when there is only 1 row in the temp table, but when I have more rows I get MySql Error 1175 and none of the WHERE clauses I specify are recognized.

I know that I can just disable safe mode with SET SQL_SAFE_UPDATES = 0, but can anyone tell me what I am not understanding here? Why is my WHERE clause not accepted and why does it even need it when I do a NATURAL JOIN - and why does this only work with one row in the right table (MyTempTable)?

Code

Below is a simplified but structurally identical table creation and updates representing my problem.

- The Data Table.
Create Table MyDataTable
(
    KeyPartOne int not null,
    KeyPartTwo varchar (64) not null,
    KeyPartThree int not null,
    RelevantData varchar (200) null,
    Primary key (KeyPartOne, KeyPartTwo, KeyPartThree)
) Engine = InnoDB;

- The 'Temp' table.
Create Table MyTempTable
(
    KeyPartOne int not null,
    KeyPartTwo varchar (64) not null,
    KeyPartThree int not null,
    Primary key (KeyPartOne, KeyPartTwo, KeyPartThree)
) Engine = Memory;

- The Update Query (works fine with only 1 row in Temp table)
update MyDataTable natural join MyTempTable 
set RelevantData = 'Something Meaningful';

- Specifying 'where' - roduces same effect as the other update query
update MyDataTable mdt join MyTempTable mtt
on mdt.KeyPartOne = mtt.KeyPartOne
and mdt.KeyPartTwo = mtt.KeyPartTwo
and mdt.KeyPartThree = mtt.KeyPartThree
set RelevantData = 'Something Meaningful'
where mdt.KeyPartOne = mtt.KeyPartOne
and mdt.KeyPartTwo = mtt.KeyPartTwo
and mdt.KeyPartThree = mtt.KeyPartThree;

PS Both of the above update statements work as expected when the temp table only contains one row, but give me an error when there is more than one row. I am very curious why!

+3


source to share


2 answers


In the MySql forum, update requests are valid, and the fact that they do not work in the Workbench with Safe Update Mode enabled does not indicate that there is anything wrong with the index. This is just a quirk of the "don't shoot yourself" Workbench. :-)



+1


source


In the first request, UPDATE

you are using NATURAL JOIN

which is the same as NATURAL LEFT JOIN

.

In your second request, UPDATE

you are using JOIN

which is the same as INNER JOIN

.

A LEFT JOIN

does not match INNER JOIN

, a NATURAL JOIN

does not match JOIN

.



Not sure what you are trying to do, but if you are trying to update all rows in MyDataTable where the corresponding entry exists in MyTempTable, this query should do the trick:

UPDATE
    myDataTable mdt
    INNER JOIN MyTempTable mtt ON
        mdt.KeyPartOne = mtt.KeyPartOne
        AND mdt.KeyPartTwo = mtt.KeyPartTwo
        AND mdt.KeyPartThree = mtt.KeyPartThree
SET
    mdt.RelevantData = 'Something Meaningful'

If that's not what you are trying to do please clarify and I'll update the answer.

+2


source






More articles:


All Articles