1

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?

  • This is a workbench config issue. Why don't you change it from Preferences (as the message says) and use the simple first UPDATE? – ypercubeᵀᴹ Sep 19 at 10:05
  • I just want to know if there are any ways to do this without changing the settings, and also whether there are any ways to use two columns in a derived table for the WHERE statement (in general) without repeating the subquery – Nguyen H Chan Sep 19 at 10:37
0

Use correlated subquery

UPDATE `TableA`  
SET `TableA`.`enable` = 1
WHERE EXISTS ( SELECT 1
               FROM `TableB` 
               WHERE `TableA`.`code` = `TableB`.`code` 
                 AND `TableB`.`loc` = 'foo' )
  • It still gives the same error – Nguyen H Chan Sep 19 at 10:37
  • @NguyenHChan Of course, it cannot alter server mode. And nothing can make your TableA.code field unique. So this error will remain until you disable safe mode. – Akina Sep 19 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.