• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4238
  • Last Modified:

Increment a counter per row in update command in MYSQL 4

I need to set the value of sortkey to the value of num, plus an increasing counter, for all rows in a table.  The attached code is how I'd EXPECT to run it, but it fails.  I'm running MySQL 4.1.22.

Any suggestions would be welcome.  I tried ROW_COUNT, but that isn't in MySQL 4.  Neither are stored procedures.  I'd like to keep this as an SQL statement so that I don't need to extract the data and process in PHP or Perl.
SET @count=1;
UPDATE testtable SET sortkey=concat(num,@count), @count=@count+1 WHERE num = 10;
 
SET @count=1;
UPDATE testtable SET sortkey=concat(num,@count), @count=@count+1 WHERE num = 20;
 
+----+-----+---------+
| id | num | sortkey |
+----+-----+---------+
|  1 |  10 |     101 |
|  2 |  20 |     201 |
|  3 |  10 |     102 |
|  4 |  10 |     103 |
|  5 |  10 |     104 |
|  6 |  20 |     202 |
+----+-----+---------+

Open in new window

0
oreomike
Asked:
oreomike
  • 5
  • 3
1 Solution
 
oreomikeAuthor Commented:
I guess I need to mention that I need to do this for all rows in the table, but the count needs to be separate per 'num'.  If there is a way to define a hash to store counts, that may be better.

Here is my re-written attempted code, and desired table values.  Sortkey is currently NULL for all rows.
SET @count=1;
UPDATE testtable SET sortkey=concat(num,@count), @count=@count+1 
WHERE num = 10;
 
SET @count=1;
UPDATE testtable SET sortkey=concat(num,@count), @count=@count+1 
WHERE num = 20;
 
+----+-----+---------+
| id | num | sortkey |
+----+-----+---------+
|  1 |  10 |     101 |
|  2 |  20 |     201 |
|  3 |  10 |     102 |
|  4 |  10 |     103 |
|  5 |  10 |     104 |
|  6 |  20 |     202 |
+----+-----+---------+

Open in new window

0
 
Roger BaklundCommented:
Try this:
SET @count=0;
UPDATE testtable SET sortkey=concat(num,@count:=@count+1)
WHERE num = 10;
 
SET @count=0;
UPDATE testtable SET sortkey=concat(num,@count:=@count+1)
WHERE num = 20;

Open in new window

0
 
oreomikeAuthor Commented:
no, that doesn't work
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Roger BaklundCommented:
It works on my server, see below. If it does not work for you, please explain in what way it does not work: do you get an error message, do you get different results, or what?
mysql> select * from testtable;
+------+------+---------+
| id   | num  | sortkey |
+------+------+---------+
|    1 |   10 |    NULL |
|    2 |   20 |    NULL |
|    3 |   10 |    NULL |
|    4 |   10 |    NULL |
|    5 |   10 |    NULL |
|    6 |   20 |    NULL |
+------+------+---------+
6 rows in set (0.01 sec)
 
mysql> SET @count=0;
Query OK, 0 rows affected (0.04 sec)
 
mysql> UPDATE testtable SET sortkey=concat(num,@count:=@count+1)
    -> WHERE num = 10;
Query OK, 4 rows affected (0.10 sec)
Rows matched: 4  Changed: 4  Warnings: 0
 
mysql>
mysql> SET @count=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> UPDATE testtable SET sortkey=concat(num,@count:=@count+1)
    -> WHERE num = 20;
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2  Changed: 2  Warnings: 0
 
mysql> select * from testtable;
+------+------+---------+
| id   | num  | sortkey |
+------+------+---------+
|    1 |   10 |     101 |
|    2 |   20 |     201 |
|    3 |   10 |     102 |
|    4 |   10 |     103 |
|    5 |   10 |     104 |
|    6 |   20 |     202 |
+------+------+---------+
6 rows in set (0.00 sec)

Open in new window

0
 
oreomikeAuthor Commented:
I get an error message, invalid SQL, at the @count:=@count+1 part of the statement.

What version of mysql server and version are you using?  I'm using MySQL 4.1.22.  I know there is additional things like this that were added to MySQL 5.
0
 
Roger BaklundCommented:
Strange. This should also work in version 4.1, even in 4.0 and 3.23.

http://dev.mysql.com/doc/refman/4.1/en/user-variables.html

Are you running the test statements above, or are you using this in a different statement? If it is a different statement, show the query you are using and the exact error message.
0
 
oreomikeAuthor Commented:
My mistake, my command was different than I stated in the question.
0
 
oreomikeAuthor Commented:
In the question, I was setting a field to the value of the user variable separately from the update of the user variable.

What I needed to do as cxr pointed out, was to increment the variable at the same time it is assigned to the column.

See code below
mysql> update testm set sortkey=@count:=@count+1 where sortkey=0;
 
Query OK, 100 rows affected (0.01 sec)
Rows matched: 100  Changed: 100  Warnings: 0
 
 
mysql> update testm set sortkey=@count, @count:=@count+1 where sortkey=0;
 
ERROR 1064 (42000): 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 '@count:=@count+1 where sortkey=0' at line 1

Open in new window

0
 
IceColdasCommented:
UPDATE testm SET sortkey = (SELECT @rownum:=@rownum+1 AS newpos FROM  (SELECT @rownum:=0) r )
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now