update table with single select function

You may need a loop in order to accomplish what you are trying to do...

Hi, rchawdhari
I haven't been using MySQL for many years and so I chose to answer this question by using MS SQL, by slightly modifying your scripts as follows.

I've got the results for id 193 as (193, 13).

Looking over your scripts, I think you didn't get your desired results because you were over anxious and made some mistakes:

Possibel mistake 1: You entered the data as values (191,10),(192,11), (193,0). From these values your invoices are 10, 11, 0. The maximum invoice is 11, and not 12 as you expected.
Possible mistake 2: You wanted to updated 193, but your scripts said:
where id = 191. If you had wanted to update 193, then the where clause should be: where id=193.

I think you may have to try using another set of data to test your scripts before you become panicky and not trusting that your method in fact works.

Best regards
Old Std.

My scripts appear below:

Use TestDB
go

create table dummy (id int ,invoice int)

insert into dummy values(191,10),(192,11),(193,12);

update dummy set invoice=(select max(invoice) from dummy )+1
where id=193

Select * from dummy

/* results:

id invoice
191 10
192 11
193 13

*/

My MS SQL scripts appear below:

Use TestDB
go

create table dummy (id int ,invoice int)

insert into dummy values(191,10),(192,11),(193,12);

update dummy set invoice=(select max(invoice) from dummy )+1
where id=193

Select * from dummy

/* results:

id invoice
191 10
192 11
193 13

*/

You can cancel the previous action performed by clicking on the button from.
a. Undo, ribbon
b. Undo, customise quick access toolbar
c. Redo, ribbon
d. Redo, customise quick access toolbar