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.
You can try the following solution:
SET @count = 64; -- so the first increment is 65 (starting on A).
UPDATE table_name t
SET t.Revision = CHAR(@count:=@count+1)
WHERE t.hidden = 0;
or (shorter):
UPDATE table_name t, (SELECT @count:=64) t2
SET t.Revision = CHAR(@count:=@count+1)
WHERE t.Hidden = 0;