How to increment using a SQL variable within an update statement

Question

I am trying to increment a column using an @count variable in SQL. I have tried multiple attempts that I will list below that all result in: Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...

First was:

SET @count = 65;
UPDATE table t
SET t.Revision = CHAR(@count)
, @count = @count + 1
WHERE t.hidden = 0;

I am trying to increment every row currently as a proof of concept that this works.

Second was:

DECLARE t CURSOR FOR
SELECT * FROM table
WHERE t.hidden = 0;

OPEN t;
FETCH NEXT FROM t;

WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE table t2 SET t2.Revision = 'D' WHERE t2.id1 = t.id1 AND t2.id2 = t.id2;
    END;
END

CLOSE t;
DEALLOCATE t;

Once again I am just trying to see if I can set a standard variable using a while loop before I implement incrementing as a proof of concept that it works.

I am not sure why either of these attempts is failing but any help would be appreciated.

Answer 1

Here is how your first example should work(inside of some loop):

first you set your count value, then you update

SET @count = 65;
UPDATE CUSTOMER t
SET t.LName = CONVERT(@count, char)
where t.FName = 'a';

...and then increase that count and you update again...

set @count = @count + 1;
UPDATE CUSTOMER t
SET t.LName = CONVERT(@count, char)
where t.FName = 'a';

But that should be in a procedure for example.

Here is the DEMO. I it a small example and I hope you will find it helpful. Cheers!