-1

I am running MySQL Workbench 6.3.8 b1228 CE (64-bit) in "safe update mode". I am trying to run a query with a "WHERE" statement, but it still returns "Error 1175"

Here is my query statement:

DELETE FROM `my_db`.`table_name` WHERE `email` = 'john@smith.com';

(Obviously "my_db" and "table_name" are placeholders.)

Why would Workbench throw Error 1175 for this query when I have the most basic of "WHERE" statements included?

Mark Larson
  • 63
  • 1
  • 8
  • Did you Google that error? Many hits found, one of which being http://stackoverflow.com/q/11448068/1415724 and another http://stackoverflow.com/q/18767308/1415724 where one of those could be possible duplicates. – Funk Forty Niner Dec 09 '16 at 15:28
  • 1
    The important part of the error is : "...without a WHERE that uses a KEY column". So i have to ask is the email column a key column? – Arnolio Dec 09 '16 at 15:36
  • Fred, I did Google it, and found many, such as those you posted, that explained how to disable the error by removing SAFE UPDATES mode. What I wanted to know what WHY my query was throwing the error. That is what confused me. Arnolio nailed it. Thanks for responding! – Mark Larson Dec 09 '16 at 23:08

5 Answers5

1

In Sql by default safe options is enabled which restricts the user from deleting or updating the data in table using inappropriate key.To turn of the safe update go to

edit->preferences->sql editor and uncheck the safe update check box at the bottom of the preference window or even you can delete or update referencing the primary key.

For Example: Let us consider we have table called user_details with four fields(name, number, email ,address) where name is the primary key. So your delete statement would look like this

Delete from user_details where name="xxx";

Note: Reconnect to the database after safe update is turned off. Even you can restart the server but reconnect itself works fine.

Hope this solves your issue.

maxshuty
  • 9,708
  • 13
  • 64
  • 77
Arun Kumaar
  • 28
  • 6
0

maybe is a very simple error. Try delete the row from the user interface. see the attached images.

Select your table and right click in the grid view

press in apply to see the sql

here you will see the final query used by mysql

Rolando
  • 752
  • 1
  • 14
  • 41
0

As Arnolio mentions in a comment,

The important part of the error is : "...without a WHERE that uses a KEY column". So i have to ask is the email column a key column?

I wasn't focused on the KEY portion, and my email column wasn't a KEY, which caused the error. Thanks, Arnolio!

Community
  • 1
  • 1
Mark Larson
  • 63
  • 1
  • 8
0

Consider:

update
    civicrm_address
set
    geo_code_1 = 99999
and
    id is not null

This throws the same error but,

update
    civicrm_address
set
    geo_code_1 = 99999
and
    id <> 0

is successful

Why doesn't the first one work?

(I wanted to post this as a comment but it doesn't allow the formatting)

Dennis M. Gray
  • 332
  • 1
  • 3
  • 17
0

I ran into a similar issue! My SQL statement would not run when I ran this query:

DELETE FROM users_delete, WHERE password = 'password';

But worked fine when when I ran this query (see the comma is gone):

DELETE FROM users_delete WHERE password = 'password' AND id = 1;

Safe mode was also off in case you were wondering.

Rogue
  • 11,105
  • 5
  • 45
  • 71
Aleksandra Neskovic
  • 1