0

Without shutting off safe update, how to write a Where clause that checks for a certain entry of a KEY column?"

'Cause basically every answer I've seen is "Shut off Safe Update", either temporarily, or permanently.

Although, Nic3500 did point me to an answer by Rudy De Volde where you search for where the key column doesn't exist.

But I'm trying to select a row by it's key column, and that isn't working.

Is Safe Update too broken to use? Or is it just the simplest solution that no one is giving ways to actually fix the error?

Although, I did copy try to update a table I created in my own database, and with:

create table z.country AS (SELECT * FROM world.country);

insert into z.country (Code, name, continent, population) values ("NHZ", "Nariza", "Antarctica", 6523);

This works:

Update WORLD.country set Name = "Noziland" where name = "Aruba" and Code <> "ABC" and Code <> "NHA";

These don't:

Update z.country set Name = "Noziland" where name = "Aruba" and Code <> "ABC" and Code <> "NHA";

Update z.country set Name = "Noziland" where Continent = "Antarctica" and Code <> "NHC" and Code <> "NHA";

EDIT: Oh, the Select * method of copying doesn't preserve primary keys! I have to add that manually!

Malady
  • 251
  • 1
  • 12
  • 1
    Look at https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench, the answer with 23 upvotes shows an example of how to do it (from Rudy De Volder). – Nic3500 Nov 15 '17 at 04:59
  • Given the data you have shown the first query would not update name because there is no name = Aruba in the data, the second query would not update for the same reason and the third query does work because all the conditions in the where clause are satisfied. So I am confused about what the problem is. – P.Salmon Nov 15 '17 at 07:15
  • @P.Salmon - Isn't z.country a copy of world.country? Why wouldn't it have Aruba, if world.country does? – Malady Nov 15 '17 at 16:08
  • 1
    It would have been useful if you had mentioned that world is a mysql sample database and included a link where to find it. – P.Salmon Nov 16 '17 at 08:40

0 Answers0