unable to use if else and union at a time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

unable to use if else and union at a time

Author

Topic

suni_dotnet
Starting Member

2 Posts

Posted - 2009-03-06 : 09:42:12

Hi All,
iam using if else for updating and inserting, i need to do this for 3 table how can i unite them with 2unions..

IF EXISTS (SELECT col1,col2,col3,col4
FROM TableB)

UPDATE TableB
SET col2=TableA.col2,
col3=TableA.col3,
col4=TableA.col4
from TableA
where (TableB.col1 = TableA.col1
and TableA.Col5='no')

ELSE
INSERT INTO TableB (col1,col2,col3,col4)
select TableA.col1,TableA.col2,TableA.col3,TableA.col4

from TableA
where TableA.Col5='no';

and i need to repeat this code for TableC and TableD (instead of TableA) for updating TableB. How can i unite all in one procedure.Hope iam clear if not i can...Please help...
Thanks in advance

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 18:30:13

[code]
UPDATE TableB
SET col2=TableA.col2,
col3=TableA.col3,
col4=TableA.col4
from TableA
Inner join TableB
On TableB.col1 = TableA.col1
and TableA.Col5='no'

INSERT INTO TableB
Select t.* from Table t Left join TableB b
on t.col1 = b.col1 and t.col5 = 'no'
Where b.col1 is null[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:12:10

[code]
UPDATE b
SET b.col2=t.col2,
b.col3=t.col3,
b.col4=t.col4
from TableB b
JOIN (SELECT col1,col2,col3,col4 FROM TableA
UNION
SELECT col1,col2,col3,col4 FROM TableC
UNION
SELECT col1,col2,col3,col4 FROM TableD
)t
ON t.col1 = b.col1
WHERE t.Col5='no'

INSERT INTO TableB (col1,col2,col3,col4)
select t.col1,t.col2,t.col3,t.col4
from (SELECT col1,col2,col3,col4 FROM TableA
UNION
SELECT col1,col2,col3,col4 FROM TableC
UNION
SELECT col1,col2,col3,col4 FROM TableD
)t
LEFT JOIN TableB b
ON t.col1 = b.col1
where t.Col5='no'
AND b.col1 IS NULL
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources