CODEWITHSUNDEEP
mysql
71GA
71GA

Reputation: 1391

MySQL - table specified twice both as a target for update and as a separate source for data

When I try to execute these two queries:

UPDATE bills
SET id_bill_tmp = (SELECT IFNULL(id_bill_tmp, 0)+1 AS id_bill_tmp FROM bills)
WHERE id_interess = 1;

UPDATE bills
SET id_bill_tmp = (SELECT max(id_bill_tmp)+1 FROM bills)
WHERE id_interess = 1;

I get:

table 'bills' is specified twice both as a target for 'update' and as a separate source for data

How can I rewrite UPDATE to not report this error?

Upvotes: 0

Views: 1886

Answers (1)

jafarbtech
jafarbtech

Reputation: 7015

You cant update the table by getting the data from the same table in a single query. For this at least you should use a temporary table or else you should go with a view

Create a view with your select query

CREATE VIEW `view_name` AS SELECT IFNULL(id_bill_tmp, 0)+1 AS id_bill_tmp FROM bills

and then update the table using the view

UPDATE bills
SET id_bill_tmp = (SELECT id_bill_tmp FROM view_name)
WHERE id_interess = 1;

Upvotes: 1

Related Questions