Multiple update in mysql using a subquery

Question

Please, I want a query that will help me to update the first 200 rows of the bill column to 3 after selecting the first 200 rows from the dlr table. I also want there to be no repetition when my select statement is coming back to pick another 200 rows.

It will only pick the bill column rows that are not 3.

Below is the query i wrote and it is giving me below mentioned error.

'LIMIT & IN/ALL/ANY/SOME subquery:
UPDATE dlr SET bill = 3 
WHERE dlrid IN (SELECT dlrid FROM dlr WHERE bill = 0 LIMIT 0,200);

Error I am Receiving = This version of MySQL doesn't yet support

Can anybody help me out?

dee-see · Accepted Answer · 2011-08-30 15:41:18Z

3

You don't need the subquery.

UPDATE dlr SET bill = 3 WHERE bill = 0 LIMIT 200;

answered Aug 30, 2011 at 15:41

dee-see

24.1k6 gold badges63 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ypercubeᵀᴹ Over a year ago

It's generally good to use ORDER BY when using LIMIT. So you can decide the order that the action thakes place (DELETE in this case) than letting the db decide.

dee-see Over a year ago

@kayode You're welcome. Mark the answer as accepted by clicking on the check next to it if it answered your question!

mdma · Accepted Answer · 2011-08-30 15:45:23Z

0

Move the limit clause out from the subquery into the update query:

UPDATE dlr SET bill = 3 WHERE dlrid IN (SELECT dlrid FROM dlr WHERE bill = 0) LIMIT 200;

There is no need to use a range, since the next 200 is guaranteed to be distinct from the first 200, since bill changes from 0 to 3.

For this to work, dlrid must be unique in dlr, which by the name, I'm guessing it is.

answered Aug 30, 2011 at 15:45

mdma

57.9k12 gold badges97 silver badges128 bronze badges

1 Comment

kayode Over a year ago

when i tried using your query it gave this error :You can't specify target table 'dlr' for update in FROM clause.Thanks

Collectives™ on Stack Overflow

Multiple update in mysql using a subquery

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related