Updating table basing on composite primary keys from derived table

Question

I have two tables TableA and TableB defined as followings:

TableA has 4 columns id1 (Primary key), id2 (Primary key), code, enable.

TableB has 3 columns id (Primary key), code, loc.

TableB.code corresponds to TableA.code.

I would like to update TableA.enable for the entry with TableB.loc equals to foo in MySQL. Under general conditions this should do the work:

UPDATE `TableA`  
JOIN `TableB` ON `TableA`.`code` = `TableB`.`code` 
SET `TableA`.`enable` = 1
WHERE `TableB`.`loc` = 'foo'

But in MySQL Workbench I got the following error: (since TableB.loc is not a primary key)

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

So I attempted to nest a query to fetch the two primary key values from TableA and update TableA:

UPDATE `TableA` SET `TableA`.`enable` = 1
WHERE EXISTS (
    SELECT * FROM (
        SELECT 
            `TableA`.`id1` AS `id1`,
            `TableA`.`id2` AS `id2`
        FROM `TableA`
        LEFT JOIN `TableB`
        ON 
            `TableA`.`code` = `TableB`.`code`
        WHERE 
            `TableB`.`loc` = 'foo'
    ) t
    WHERE 
        t.`id1` = `TableA`.`id1` AND
        t.`id2` = `TableA`.`id2`
);

But it throws out the same error, so I tried to see if I can use the primary key column in my WHERE statement.

I also tried this query:

UPDATE `TableA`
SET `TableA`.`enable` = 1
WHERE (
    (`TableA`.`id1`, `TableA`.`id2`) = (
        SELECT `dummy`.`id1`, `dummy`.`id2` FROM (
            SELECT 
                `TableA`.`id1` AS `id1`,
                `TableA`.`id2` AS `id2`
            FROM `TableA`
            LEFT JOIN `TableB`
            ON 
                `TableA`.`code` = `TableB`.`code`
            WHERE 
                `TableB`.`loc` = 'foo'
        ) AS `dummy`
    )
);

But it also throws the error 1175.

On the other hand, I tried something like this:

UPDATE `TableA`
SET `TableA`.`enable` = 1
WHERE (
    `TableA`.`id1` = (
        SELECT `dummy`.`id1` FROM (
            SELECT 
                `TableA`.`id1` AS `id1`,
                `TableA`.`id2` AS `id2`
            FROM `TableA`
            LEFT JOIN `TableB`
            ON 
                `TableA`.`code` = `TableB`.`code`
            WHERE 
                `TableB`.`loc` = 'foo'
        ) AS `dummy`
    ) AND
    `TableA`.`id2` = (
        SELECT `dummy`.`id2` FROM (
            SELECT 
                `TableA`.`id1` AS `id1`,
                `TableA`.`id2` AS `id2`
            FROM `TableA`
            LEFT JOIN `TableB`
            ON 
                `TableA`.`code` = `TableB`.`code`
            WHERE 
                `TableB`.`loc` = 'foo'
        ) AS `dummy`
    )
);

It works, but by repeating the subquery for dummy twice the code would be very clumsy. It is obvious to me that I can disable the safe update mode to perform this query, but I just hope to know if there are any methods for matching the rows using the two primary keys (TableA.id1 and TableA.id2) without repeating the dummy derived table and without disabling the safe update mode?

Answer 1

Use correlated subquery

UPDATE `TableA`  
SET `TableA`.`enable` = 1
WHERE EXISTS ( SELECT 1
               FROM `TableB` 
               WHERE `TableA`.`code` = `TableB`.`code` 
                 AND `TableB`.`loc` = 'foo' )