if statements in mysql?

Question

is there an if statement when it comes to mysql query statements?

when i am updating a table record, i want to only update certain columns if they have a value to be updated.

for example, i want an update table function, and there is a table for volunteers and a table for people who just want email updates.

i want to use the same function (there will be a function that only deals w/ the upd queries) and is it possible to do this in theory...

if you are updating volunteer table, only update these columns, if mailing_list, then update these

i know this can by done using an if statement w/ two query statements, based on what table you're updating, but i am wondering is it possible to use only one query statement w/ the conditionals in it to update the appropriate columns in the table.

this may sound like something you would dream about, let me know.

thanks.

Answer 1

I think this should work:

UPDATE volunteer, people
SET volunteer.email = '[email protected]',
    people.email = '[email protected]',
    people.first_name = 'first',
WHERE people.id = 2 AND volunteer.id = 5;

I got this from the update syntax on the MySQL website.

Answer 2

You could do this in one query, but it's not making sense to me why you would want to. Maybe describe the arguments to your desired function and what effect they would have?

You can conditionally update something like this:

update tablereferences
    set foo.bar = if( somebooleanexpression, newbarvalue, foo.bar ),
        baz.quux = if( somebooleanexpression, newbazvalue, baz.quux )
    where ...

allowing you to use the same query but control which tables are updated.