CODEWITHSUNDEEP
mysqlselectjoin
Josh Hancock
Josh Hancock

Reputation: 17

MySQL Update table with Select/Join

Please note: I am using MySQL Workbench for this so some errors may be related to that specifically (I'm not 100% sure I'm afraid)

Hi,

I am working on a project for my Uni where I accept a CSV file containing a list of guests and information such as the course area they are interested in. The CSV file will contain the actual name of the Area of Interest as this comes from a registration web page (for Open Days).

I am trying to take the Area of Interest in my Guests (joinTest) table, do a join to the Interests table (that contains the ID numbers for them as well as the Interest Name) and populate the InterestID column in the Guests table.

I am using a test table for now until I've got the SQL right. This is the SQL I am currently using:

UPDATE joinTest
SET interestID = 
 (
   SELECT interest.interestID FROM joinTest 
   LEFT JOIN interest on joinTest.interestName = interest.interestName
 )
WHERE interestID IS NULL;

I've tried that and get the error

Error Code: 1093. You can't specify target table 'joinTest' for update in FROM clause

So I'm assuming I can't use a SELECT clause while join a join in an UPDATE.

I then tried this:

UPDATE joinTest
INNER JOIN interest
ON jointest.interestname = interest.interestName
SET joinTest.interestID = interest.interestID;

I get this 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.

I'm completely stumped now. I'm not sure where to go from here.

If any more information is required, please let me know.

Upvotes: 0

Views: 338

Answers (2)

Devon Bessemer
Devon Bessemer

Reputation: 35357

SQL_SAFE_UPDATES is disabled by default but Workbench enables it. Like the error message says: toggle the option in Preferences -> SQL Queries and reconnect.

This error shouldn't exist unless you specifically turn on SQL_SAFE_UPDATES in your web app (php).

Upvotes: 1

Yaroslav Admin
Yaroslav Admin

Reputation: 14535

It's MySQL Workbench specific thing. You can disable this behavior in settings as your error message advices you: ... toggle the option in Preferences -> SQL Queries and reconnect.

PS You can safely run this query in your code without SET SQL_SAFE_UPDATES = 0;.

Upvotes: 1

Related Questions