Comments on: Multi condition UPDATE query

By: Gaurav Rupani

Gaurav Rupani — Tue, 15 Feb 2011 03:59:59 +0000

I exactly needed something like that… Thnxx for dis Logic…..

By: nadavkav

nadavkav — Thu, 27 Jan 2011 20:31:54 +0000

Very usefull! thanks 🙂

By: Roland Bouman

Roland Bouman — Thu, 27 Jan 2011 19:56:22 +0000

it’s a pity the manual calls this “control-flow-functions”. I proposed “conditional expressions” some time ago, but they stick to this term because it has always been there.

By: shlomi

shlomi — Thu, 27 Jan 2011 17:39:58 +0000

Roland, thank you for expanding on this. Xerxes, see this link: http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html in the MySQL manual.

By: Roland Bouman

Roland Bouman — Thu, 27 Jan 2011 13:48:54 +0000

Nice writeup, good trick 🙂

@xerxes like shlomi says, there are two different kind of IFs, IF function and IF statement. Likewise there are two different CASEs, CASE expression (or operator if you like that better) and CASE statement. And each of these come in two flavors, simple (CASE expr WHEN val1 THEN …. END) and searched (CASE WHEN condition1 THEN … END CASE)

By: shlomi

shlomi — Thu, 27 Jan 2011 13:09:35 +0000

@xerxes,
This is not the same IF() as in stored routine’s IF. It is not a control flow construct but a simple function.

CASE is ANSI. But IF() is so much shorter to write…

By: xerxes

xerxes — Thu, 27 Jan 2011 12:18:36 +0000

Technically IF is a flow-control construct – I believe CASE would be more appropriate here.

By: a

Thu, 27 Jan 2011 09:08:45 +0000

I don’t know how it is on mysql, but on another db (don’t remember right now), the overhead of if is that it calculate the two options in any case. if there is a big calculation in one case (or both of them) – there will be significant overhead.