Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update query failing with error : 1175

Tags:

sql

mysql

I am trying to update a table using the following query

update at_product A join
(
SELECT atbillfields.billeditemguid,count(*) AS numberOfPeopleBought
    ,sum(atbillfields.billeditemqty) AS soldquantity
FROM jtbillingtest.at_salesbill atsalesbill 
JOIN jtbillingtest.at_billfields atbillfields
    ON atsalesbill.billbatchguid=atbillfields.billbatchguid
WHERE atsalesbill.billcreationdate BETWEEN '2013-09-09' AND date_add('2013-09-09', INTERVAL 1 DAY)
GROUP BY atbillfields.billeditemguid) B ON B.billeditemguid = A.productguid
SET A.productQuantity = A.productQuantity - B.soldquantity

But, getting the following exception:

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.

When I gave a where clause with the update like A.productQuantity = 1, it updated that particular.

Can someone point why I am unable to execute the query and how to solve the issue?

like image 792
Firu Avatar asked Sep 12 '13 14:09

Firu


People also ask

How do I turn off safe mode in SQL?

Go to Edit –> Preferences. Click "SQL Editor" tab and uncheck "Safe Updates" check box. Query –> Reconnect to Server // logout and then login. Now execute your SQL query.

How do I turn off safe mode in SQL Workbench?

You also can disable safe mode in MySQL Workbench, go to Edit -> Preferences -> SQL Editor, and uncheck "Safe Updates" check box. Then reconnect to MySQL server by going to Query -> Reconnect to Server.


2 Answers

Have a look at:

http://justalittlebrain.wordpress.com/2010/09/15/you-are-using-safe-update-mode-and-you-tried-to-update-a-table-without-a-where-that-uses-a-key-column/

If you want to update without a where key you must execute

SET SQL_SAFE_UPDATES=0;

right before your query.

Another option is to rewrite your query o include a key.

like image 162
Menelaos Avatar answered Nov 03 '22 08:11

Menelaos


This error means you're operating in safe update mode and therefore you have two options:

  • you need to provide a where clause that includes an index for the update to be successful or
  • You can disable this feature by doing SET SQL_SAFE_UPDATES = 0;
like image 37
Joe Minichino Avatar answered Nov 03 '22 09:11

Joe Minichino