mysql update query with select as criteria

Question

UPDATE reservation SET flag = "1" WHERE ipAddress = (SELECT ipAddress FROM reservation WHERE endDate < CURRENT_TIMESTAMP);

Im trying to use this query for changing flag column of those entries in reservation table whose date has expired. The flag column is by default 0. so im trying to change expired once to 1 for my identification.

Im getting the following error.

ERROR 1093 (HY000): You can't specify target table 'reservation' for updatein FROM clause

Can someone suggest a solution to this problem..

score 3 · Accepted Answer · answered Aug 16 '12 at 10:07

3

if you're using the same table in the subselect, you can probably omit the subselect altogether. why dont you use the following instead

UPDATE reservation 
SET flag = "1" 
WHERE endDate < CURRENT_TIMESTAMP

answered Aug 16 '12 at 10:07

gabtub

1,460
2
18
21

oh man i was simply trying to over complicate things!! thanks a lot :) – Bala Aug 16 '12 at 10:21

score 3 · Answer 2 · answered Aug 16 '12 at 10:07

3

You do not need the inner SELECT. You just need to update the flag status based on the endDate

UPDATE reservation 
SET flag = "1" 
WHERE endDate < CURRENT_TIMESTAMP

answered Aug 16 '12 at 10:07

Taryn

242,637
56
362
405

mysql update query with select as criteria

2 Answers2