Update Field in Database A With Field From Database B and Avoiding Safe Update Warning

Question

I want to update the field xfade in samdb.songlist with the values from xfade in another database (on the same host), samdbtmp.songlisttmp. As long as there is the text APPLE in the filename field.

Here is my attempt...

UPDATE samdb.songlist
SET 
    samdb.songlist.xfade = (SELECT 
            samdbtmp.songlisttmp.xfade
        FROM
            samdbtmp.songlisttmp
        WHERE
            samdbtmp.songlisttmp.ID = samdb.songlist.ID)
WHERE filename LIKE '%201411.mp3';

I would like to do this without taking off safe updates in Workbench, and I thought by adding the WHERE, with key field ID, I should be able to do that. But I get error...

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 Queries and reconnect.

Question... is my MySQL correct? And how do I avoid the error?

Answer 1

Not really an answer as much as a workaround. I went with the below...

SET SQL_SAFE_UPDATES=0;
UPDATE samdb.songlist
SET 
    samdb.songlist.xfade = (SELECT 
            samdbtmp.songlisttmp.xfade
        FROM
            samdbtmp.songlisttmp
        WHERE
            samdbtmp.songlisttmp.ID = samdb.songlist.ID)
WHERE filename LIKE '%201411.mp3';
SET SQL_SAFE_UPDATES=1;

I was happy to have safe updates disabled for this query, just not permanently off.