compare 2 table and update in necessary

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

SQL Server 2000 Forums

SQL Server Development (2000)

compare 2 table and update in necessary

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-07-23 : 08:22:22

Hi

I have a 2 tables that looks like this...

Table 1

ID
NodeID
CustID
InfoText

Table 2

ID
NodeID
CustID
IsActive

I need to create a stored procedure that check loop through all NodeID in Table 1 and check if column: NodeID, CustID (CustID = 1) and IsActive (IsActive = 1) exsits in Table 2. If they don't exsits I need to add those missing. And if they exsits but have the IsActive = 0, then I need to update that record so it is equal to 1.

Can someone show me how to do this?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-23 : 09:01:36

update table2
set IsActive = 1
from table2 t1
join table1 t1
on t1.NodeID = t2.NodeID
and t1.CustID = t2.CustID
where t2.IsActive = 0

insert table2
(
NodeID ,
CustID ,
IsActive
)
select t1.NodeID ,
t1.CustID ,
1
from table1 t1
left join table2 t2
on t1.NodeID = t2.NodeID
and t1.CustID = t2.CustID
where t2.NodeID is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-07-23 : 13:35:23

Hi

Thanks for the code, excellent!
I just want to check one thing though. Is theese lines really supposed to be like this...

update table2
set IsActive = 1
from table2 t1
join table1 t1

and not like this...?

update table2
set IsActive = 1
from table2 t1
join table1 t2

Best Regards

Kristen
Test

22859 Posts

Posted - 2006-07-23 : 13:42:46

I imagine Nigel meant it to be:

update table2
set IsActive = 1
from table2 t2
join table1 t1

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-23 : 15:21:56

Yep - would give a compil error.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources