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.

Are you trying to update two tables in one query? It's not very clear from your question — Eran Galperin
– Eran Galperin, Commented Oct 10, 2008 at 2:25
so, to clarify. You want one SQL statement to update two different tables which have different field names? something like: UPDATE volunteers, mailing_list SET IF(table = volunteers, volEmail, mailEmail) = 'xyz'; — nickf
– nickf, Commented Oct 10, 2008 at 2:26
@nickf - yes exactly, so when I go to make changes down the road, I don't have to make a change to multiple items, but one query. or should i have a different query for each instance, invoking the appropriate one by a switch/case statement. let me know what is more practical. — Brad
– Brad, Commented Oct 10, 2008 at 2:29
It makes me think you might be better off combining the tables if they are similar enough in nature that you are switching between similar queries — Eran Galperin
– Eran Galperin, Commented Oct 10, 2008 at 2:38

Darryl Hein · Accepted Answer · 2008-10-10 03:25:06Z

3

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.

answered Oct 10, 2008 at 3:25

Darryl Hein

145k96 gold badges224 silver badges265 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ysth · Accepted Answer · 2008-10-10 04:41:29Z

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.

Collectives™ on Stack Overflow

if statements in mysql?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related