PCFormat Forum Archive

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:

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?

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???



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;

If that's what you're getting at, I'd write a stored procedure to do the job - not nearly as messy.

example please!!!??? Using my wacky sql...hahaha.. thankz!!!

pilgrim007 wrote:example please!!!??? Using my wacky sql...hahaha.. thankz!!!

SQL Stored Procedures For Beginners