CODEWITHSUNDEEP
mysql
pradipta
pradipta

Reputation: 16

How to handle versioning in sql with single query

Each Lead put data into pre_order_quotation table with a new version each time. I am trying to get row count of all quotation by a lead & add 1 to it to get the version of new entry of quotation . Was trying to achieve it in single query

insert into pre_order_quotation (lead_id,version,created_at,updated_at,file_name) values (1405,(select count(*) from pre_order_quotation where lead_id = 1405)+1,'2020-08-22 12:13:51','2020-08-22 12:13:51','dummy-5f410bffbcc80.pdf')

But i am getting the following error :

You can't specify target table 'pre_order_quotation' for update in FROM clause

How can i achieve it in single query?

Upvotes: 0

Views: 27

Answers (1)

P.Salmon
P.Salmon

Reputation: 17640

Bury the increment a bit deeper..

insert into pre_order_quotation (lead_id,version,created_at,updated_at,file_name) 
values (1405,
(select cnt from (select count(*) + 1 cnt from pre_order_quotation where lead_id = 1405) s ),
'2020-08-22 12:13:51','2020-08-22 12:13:51','dummy-5f410bffbcc80.pdf');

Upvotes: 1

Related Questions