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
