Updating table basing on composite primary keys from derived table

1 vote
1 answer
5305 views
                          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**?

                        
Asked by HYChan (9 rep)
Sep 19, 2019, 10:02 AM
Last activity: Apr 14, 2025, 04:11 AM
Updating table basing on composite primary keys from derived table

Related Questions
