CODEWITHSUNDEEP
mysqljoin
Naveen Kumar
Naveen Kumar

Reputation: 4601

Update multiple rows, but only first row with different value

table 

create table tst(locationId int,
                 scheduleCount tinyint(1) DEFAULT 0,
                 displayFlag tinyint(1) DEFAULT 0);

INSERT INTO tst(locationId,scheduleCount)
values(5,0),(2,0),(5,1),(5,2),(2,1),(2,2);

I update multiple rows and multiple columns with one query, but want to change the one of the columns only for the first row and keep the other things the same for that column.

I want to update all the rows with some location id and change displayFlag to 1 and increment scheduleCount of only the top entry with 1 , rest would remain the same

**Query **

update tst,(select @rownum:=0) r,
       set tst.displayFlag =1, 
       scheduleCount = (CASE WHEN @rownum=0
                             then scheduleCount+1
                             ELSE scheduleCount
                        END), 
       @rownum:=1 where locationId = 5

But it gives error and does not set the user defined variable rownum, I am able to join the tables in a select and change the value of the rownum, is there any other way to update the values.

Upvotes: 0

Views: 2018

Answers (1)

Arth
Arth

Reputation: 13110

I'm not sure this is the correct way of doing such a thing, but it is possible to include the user variable logic in the CASE condition:

UPDATE tst
  JOIN (SELECT @first_row := 1) r
   SET tst.displayFlag = 1, 
       scheduleCount = CASE 
         WHEN @first_row = 1 AND ((@first_row := 0) OR TRUE) THEN scheduleCount+1
         ELSE scheduleCount
       END
 WHERE locationId = 5;

I have used a @first_row flag as this is more inline with your initial attempt.

The CASE works as follows:

  • On the first row @first_row = 1 so the second part of the WHEN after AND is processed, setting @first_row := 0. Unfortunately for us, the assignment returns 0, hence the OR TRUE to ensure the condition as a whole is TRUE. Thus scheduleCount + 1 is used.

  • On the second row @first_row != 1 so the condition is FALSE, the second part of the WHEN after AND is not processed and the ELSE scheduleCount is used.

You can see it working in this SQL Fiddle. Note; I have had to set the column types to TINYINT(3) to get the correct results.

N.B. Without an ORDER BY there is no guarantee as to what the '1st' row will be; not even that it will be the 1st as returned by a SELECT * FROM tst.

UPDATE

Unfortunately one cannot add an ORDER BY if there is a join.. so you have a choice:

Initialise @first_row outside the query and remove the JOIN.

Otherwise you are probably better off rewriting the query to something similar to:

UPDATE tst
  JOIN (
    SELECT locationId,
           scheduleCount,
           displayFlag,
           @row_number := @row_number + 1 AS row_number
      FROM tst 
      JOIN (SELECT @row_number := 0) init
     WHERE locationId = 5
  ORDER BY scheduleCount DESC
       ) tst2
    ON tst2.locationId = tst.locationId 
   AND tst2.scheduleCount = tst.scheduleCount 
   AND tst2.displayFlag = tst.displayFlag 
   SET tst.displayFlag = 1, 
       tst.scheduleCount = CASE  
         WHEN tst2.row_number = 1 THEN tst.scheduleCount+1
         ELSE tst.scheduleCount
       END;

Or write two queries:

  UPDATE tst
     SET displayFlag = 1
   WHERE locationId = 5;

  UPDATE tst
     SET scheduleCount = scheduleCount + 1 
   WHERE locationId = 5
ORDER BY scheduleCount DESC
   LIMIT 1;

Upvotes: 1

Related Questions