Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1

    Question Unanswered: mySQL adding 1 to a number for multiple updated rows

    Hey all i have the following update for my table in mySQL:
    Code:
        $dbBuilder = '';
    		
    	foreach($_POST as $key => $value)
    	{
    		if ($value != 'APPROVE') {
    			$dbBuilder = $value . ", " . $dbBuilder;
    		}
    	}
    
    	$dbBuilder = '(' . rtrim($dbBuilder, ", ") . ')';
    
        UPDATE userCase SET Accepted = 1 WHERE CaseNum in $dbBuilder
    Works fine as it should... but now i needed to modify it to allow a new QNum for each of whatever CaseNum.
    Code:
        $result = mysql_query("SELECT QNum FROM userCase ORDER BY QNum DESC limit 1");
    
    	while($row = mysql_fetch_assoc($result)) {
    		$QNum = $row["QNum"] + 1;
    		break;
    	}
    
        UPDATE userCase SET Accepted = 1, QNum = " . $QNum . " WHERE CaseNum in $dbBuilder
    Doing it that way above would set QNum to the same number for all CaseNum in "in". It needs to be unique (as in, adding a 1 to the QNum for each new row).

    It only needs to be 1 more than the last QNum in the table. So when someone "approves" a few things (lets say 3 things), it goes to the query above and it needs to be (lets say the last number is 4 in the DB) so it needs to be 5,6,7. The code i have now would just do 5,5,5.

    How would i accomplish this using the same CaseNum in... code above?

    Thanks!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    It is possible. I have setup a test case but you could easily modify this slightly to make it fit inline with what you have there.

    Basically what you do is use user variables which will act as the counter. You will join it to the rows you are getting back so that it returns incremental values.
    Code:
    mysql> create table testupdate (name varchar(10), id integer);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into testupdate(name) values ('tom'),('dick'),('harry');
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> set @x := 0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update testupdate t join (select name, @x := @x + 1 val from testupdate where name in ('tom','harry')) as tmp set id = val where t.name in ('tom','harry') and t.name = tmp.name;
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    
    mysql> select * from testupdate;
    +-------+------+
    | name  | id   |
    +-------+------+
    | tom   |    1 | 
    | dick  | NULL | 
    | harry |    2 | 
    +-------+------+
    3 rows in set (0.00 sec)
    In this example you will see that I have a select statement which is incrementing the @x user variable. If we were to take this statement and view the results we would get the following:

    Code:
    mysql> set @x:=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select name, @x := @x + 1 val from testupdate where name in ('tom','harry');
    +-------+------+
    | name  | val  |
    +-------+------+
    | tom   |    1 | 
    | harry |    2 | 
    +-------+------+
    2 rows in set (0.00 sec)
    By using this information and linking back with the key we can then perform the update. If you set the user variable @x to another value and re-run you will see that it will continue working.

    Code:
    mysql> set @x:=99;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select name, @x := @x + 1 val from testupdate where name in ('tom','harry');
    +-------+------+
    | name  | val  |
    +-------+------+
    | tom   |  100 | 
    | harry |  101 | 
    +-------+------+
    2 rows in set (0.01 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •