0

i have a table named itemorder with: userID, itemID, date, status, notes in my MYSQL DB. The PK of the table is userID, itemID

I need to write an SQL query that will delete all rows who are 2 days old and status = 2. (this sql query will run in my server once a day).

I've written the following sql query:

SELECT * 
FROM itemorder 
WHERE
  statusOrder=2
  AND statusDate< (SELECT DATE_ADD(CURDATE(), INTERVAL -2 DAY))

the query returns all rows who are match to condition. however if i change the SELECT * to DELETE it doesn't work.

here is the code

DELETE
FROM itemorder 
WHERE
  statusOrder=2
  AND statusDate<(SELECT DATE_ADD(CURDATE(), INTERVAL -2 DAY))

it says: 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 Editor -> Query Editor and reconnect.

from the error i understood that i can not delete as the query WHERE doesn't identify each rows by it's PK. what can i do? I read the topic Delete duplicate records from a SQL table without a primary key however still couldn't figure how to change my SELECT to DELETE.

Community
  • 1
  • 1
ronn jack
  • 181
  • 1
  • 3
  • 12

3 Answers3

0

try this:

SET SQL_SAFE_UPDATES=0;
DELETE FROM itemorder WHERE statusOrder=2 AND datediff(now(), statusDate) > 2

It should work

BackSlash
  • 21,927
  • 22
  • 96
  • 136
  • Ok re-updated, in fact it was the "AS" clause, now it works for me, just tested it, let me know if it works for you! – BackSlash Jan 22 '13 at 20:21
  • nop.. same error... are u sure that in ur test u set the PK right? – ronn jack Jan 22 '13 at 20:25
  • 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 Editor -> Query Editor and reconnect. – ronn jack Jan 22 '13 at 20:28
0

Try this as a workaround:

DELETE
FROM itemorder 
WHERE UserID & '-' & ItemID in (
SELECT UserID & '-' & ItemID FROM itemorder
WHERE statusOrder=2 AND statusDate<(SELECT DATE_ADD(CURDATE(), INTERVAL -2 DAY))
)
Lord Peter
  • 3,433
  • 2
  • 33
  • 33
  • what is the syntax for "YourPK " ? i wrote UserID and itemID. but it doesnt work. it says Error Code: 1241. Operand should contain 1 column(s) – ronn jack Jan 22 '13 at 20:21
  • MySQL apparently does not support where (col1, col2) in (select col1, col2...), so you would have to use something like: where userID & '-' & itemID in (select userID & '-' & itemID... FROM itemorder...) - have edited post – Lord Peter Jan 22 '13 at 20:27
  • this gives another error Error Code: 1093. You can't specify target table 'itemorder' for update in FROM clause – ronn jack Jan 22 '13 at 20:29
  • You're right - just tried in SQL Workbench. You will just have to disable safe update mode. (In SQL Workbench it's Edit/Preferences/SQL Editor - clear "Safe Updates" in Query Editor section and reconnect to your database.) You can then run almost any of the delete scripts on this page... – Lord Peter Jan 22 '13 at 20:41
0

TRY this: DELETE FROM ITEMORDER WHERE status = 2 AND status <= (NOW() - INTERVAL 2 DAY);

DEMO SQL FIDDLE

Mr. Radical
  • 1,847
  • 1
  • 19
  • 29