Having ONE select statement for Multiple Updates

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
pilgrim007
Registered User
Posts: 5
Joined: 07 Jan 2010, 10:47

Having ONE select statement for Multiple Updates

Post by pilgrim007 »

Hello,

I need some advice. I would like to have one SELECT statement which allow do multiple updates for me. At the moment, I have 5 seperate updates. So id like to get it into one select statement.

For legal reasons, I wont use the actual select statement, but I will put a rough example of what I mean:

update blablabla
set a.addrrr = b.addrr
from blablabla a, metallica b
where
...

(and I have 5 of these kinds of updates...and all of the statements reading the same tables)


Any suggestion:
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Having ONE select statement for Multiple Updates

Post by Ron2K »

I'm not quite sure what you're wanting - you want a SELECT statement that joins together all the tables that you've previously updated (in which case the JOIN keyword is your friend)? Or something else?
Kia kaha, Kia māia, Kia manawanui.
pilgrim007
Registered User
Posts: 5
Joined: 07 Jan 2010, 10:47

Re: Having ONE select statement for Multiple Updates

Post by pilgrim007 »

Ok maybe you not getting my question.

I have 5 UPDATE statements.


Both these UPDATE statements read from 2 tables, and I am joint condition with their unique/primary key (i.e. where a.id1 = b.id_no)

Both these UPDATES share at 1 least criteria (i.e. where field_name1 is in ('X',Y',Z').

However there are 1 or 2 criterias per UPDATE which are totally different from each other.
e.g. (again this is just made up...)
update 1, will have where...gogo is null
update 2, will have where ...popo is null
update 3, will have where ...soso is null etc etc


Each UPDATE is setting 5 fields against the other tables fields. The first table's fields are EXACTLY THE SAME for ALL the UPDATES, it is just the second table which is different from all the updates.

e.g.

update1 :
update table_choco
set a.cheese = b.cheddar
from table_choco a, yumbum b
where a.id1 = b.id_no AND
b.field_name1 is in ('X',Y',Z') AND
b.gogo is null
...
update2:
update table_choco
set a.cheese = b.melrose
from table_choco a, yumbum b
where a.id1 = b.id_no AND
b.field_name1 is in ('X',Y',Z') AND
b.popo is null
...
update3:
update table_choco
set a.cheese = b.fettacheese
from table_choco a, yumbum b
where a.id1 = b.id_no AND
b.field_name1 is in ('X',Y',Z') AND
b.soso is null

update4:
...


SO now what I am saying is that I wanna take ALL THESE 5 SQL UPDATE Statements and Make ONE statement which will do the same thing if I ran each UPDATE seperately. Still Confused???

:mrgreen:

Like Something to this effect:

SELECT
CASE
WHEN
a.id1 = b.id_no AND
b.field_name1 is in ('X',Y',Z')
THEN
(CASE
WHEN (b.gogo is null)
THEN
UPDATE table_choco
SET
a.cheese = b.cheddar
WHEN (b.popo is null)
THEN
UPDATE table_choco
SET a.cheese = b.melrose
END CASE
END CASE
FROM
table_choco a,
yumbum b;
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Having ONE select statement for Multiple Updates

Post by Ron2K »

If that's what you're getting at, I'd write a stored procedure to do the job - not nearly as messy. ;)
Kia kaha, Kia māia, Kia manawanui.
pilgrim007
Registered User
Posts: 5
Joined: 07 Jan 2010, 10:47

Re: Having ONE select statement for Multiple Updates

Post by pilgrim007 »

example please!!!??? Using my wacky sql...hahaha.. thankz!!!
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Having ONE select statement for Multiple Updates

Post by Ron2K »

pilgrim007 wrote:example please!!!??? Using my wacky sql...hahaha.. thankz!!!
SQL Stored Procedures For Beginners
Kia kaha, Kia māia, Kia manawanui.
Post Reply