CODEWITHSUNDEEP
mysqlsql
jmenezes
jmenezes

Reputation: 1926

Adding an If inside an update statement

I'm trying to add an IF clause inside an update statement. Thought this was easy, but seems it isn't.

This is the way it is. It's inside a stored procedure.

FETCH cur1 INTO procId, procType, procVals, procLen, procUpdated, procPrivate, procRegional;

    IF done THEN
      LEAVE the_loop;
    END IF;

    UPDATE scores t1
        JOIN scores t2
        ON FIND_IN_SET(t1.id, t2.vals)
        SET t1.private = t1.private+1,
        IF procType = 3 THEN // Problem lies here
             t1.regional = t1.regional+1;
            ELSE IF procType = 4 THEN
             t1.otherCol = t1.otherCol+1;
          END IF;
    WHERE t2.id = procId;

I'm stuck with the IF in there. Apart from the first SET, I also need to Update another column with the IF.

Upvotes: 0

Views: 65

Answers (3)

jerjer
jerjer

Reputation: 8760

It would be simplier to break them into 2 separate update statements, because you are updating to different fields

IF procType = 3 THEN
    UPDATE scores t1
        JOIN scores t2
        ON FIND_IN_SET(t1.id, t2.vals)
    SET t1.private = t1.private+1,
     t1.regional = t1.regional+1;
    WHERE t2.id = procId;

ELSE IF procType = 4 THEN

    UPDATE scores t1
        JOIN scores t2
        ON FIND_IN_SET(t1.id, t2.vals)
    SET t1.private = t1.private+1,
        t1.otherCol = t1.otherCol+1;
    WHERE t2.id = procId;

Upvotes: 1

Nadeem_MK
Nadeem_MK

Reputation: 7689

UPDATE scores t1
JOIN scores t2
ON FIND_IN_SET(t1.id, t2.vals)

SET t1.private = t1.private+1 
t1.regional = IF (procType = 3, t1.regional + 1, t1.regional)
t1.otherCol = IF (procType = 4, t1.otherCol + 1, t1.otherCol)
WHERE t2.id = procId

Upvotes: 1

Nadeem_MK
Nadeem_MK

Reputation: 7689

UPDATE scores t1
JOIN scores t2
ON FIND_IN_SET(t1.id, t2.vals)
SET t1.private = t1.private+1,
    CASE  procType 
    WHEN 3  THEN t1.regional = t1.regional+1
    WHEN 4  THEN t1.otherCol = t1.otherCol+1
    END as Col
WHERE t2.id = procId

Upvotes: 1

Related Questions