SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code:
    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`. Can you please assist?

    I also did like below, but everthime it's the same error:
    Code:
    SQL Error (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 'CASE procType
    Code:
    UPDATE scores t1
    JOIN scores t2
    ON FIND_IN_SET(t1.id, t2.vals)
    SET t1.private = t1.private+1,
        CASE  
        WHEN procType = 3  THEN t1.regional = t1.regional+1
        WHEN procType = 4  THEN t1.otherCol = t1.otherCol+1
        END as Col
    WHERE t2.id = procId

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    UPDATE scores t1
      JOIN scores t2
        ON FIND_IN_SET(t1.id, t2.vals)
       SET t1.private = t1.private+1
         , t1.regional = CASE WHEN procType = 3 
                              THEN t1.regional+1
                              ELSE t1.regional END
         , t1.otherCol = CASE WHEN procType = 4 
                              THEN t1.otherCol+1
                              ELSE t1.otherCol END
     WHERE t2.id = procId
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for helping. This is the way I wanted to do it. Looks like you're the same guy who replied to this question on another site.


Tags for this Thread

Bookmarks

Posting Permissions

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