Web lists-archives.com

Re: update and control flow




Hello Martin,

On 12/9/2014 9:25 AM, Martin Mueller wrote:
I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would
be very useful for update operations, but I can't get it right.

If I read the documentation correctly, it should be possible to say
something like

UPDATE X

if WORD like 'a%' SET COMMENT = 'a'
elseif WORD like 'b%' SET COMMENT = 'b'
END IF


But this gives me an error message. What am I doing wrong?


The correct syntax is to put the function after the = sign. The column name must appear by itself on the left side of the equation. You must also use the function-format of IF or a CASE..END construction.

    UPDATE X
    SET COMMENT = IF(WORD like 'a%','a',if(WORD like 'b%', 'b',COMMENT))

    UPDATE X
    SET COMMENT = CASE
      WHEN WORD like 'a%' then 'a'
      WHEN WORD like 'b%' then 'b'
      ELSE COMMENT
      END

But, as you noted, it may be more efficient to simply run two UPDATE statements each with the appropriate WHERE clause to limit the changes to just those rows that match your conditions.

    UPDATE X
    SET COMMENT = 'a'
    WHERE WORD like 'a%'

And you can combine both techniques to limit the scope of the UPDATE to just the rows to change by matching either pattern.

    UPDATE X
    SET COMMENT = IF(WORD like 'a%','a','b')
    WHERE WORD like 'a%' or WORD like 'b%'

Note: this last format doesn't need the second if() in the 'else' portion of the first IF() function because the set of rows to be operated on is already limited by the WHERE clause. The rows will match one condition or the other but not neither.

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql