3

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.

Kevin Minds
  • 43
  • 5
  • ___for the right syntax to use near ...___ And the bit following that is the most important bit. So can you show us that as well – RiggsFolly Oct 31 '19 at 15:58

2 Answers2

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!

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • Thanks, this one also worked, but Sebastian's response was closer to my exact example so it worked better. Still, I appreciate your help! – Kevin Minds Oct 31 '19 at 17:04
  • You are welcome. In my demo you have a procedure you can call and send her any number you want...so actual loop as you asked... Happy it helped. Cheers! – VBoka Oct 31 '19 at 17:06
0

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;

demo on dbfiddle.uk

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87