Mysql update with counter

Question

Whenever i try to run this query

UPDATE AddItem SET IUID=IUID+1 WHERE UID=1

I am expecting to see an incremental number

1    
2    
3

But what i actually get is

1    
1    
1

Answer 1

Use variables. SQL DEMO

update AddItem
cross join (SELECT @id := 0 ) as a
set id = @id := @id + 1
where id =1;