Update field in table if field value not exist

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

Update field in table if field value not exist

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-11 : 11:43:29

I have two tables.
AROPNFIL
ARSLMFIL

They both have the field slspsn_no.

The AROPNFIL has values in the slspn_no field that do not exist in the
ARSLMFIL.

What I would like to do is change the value to '001' if the current
value in the AROPNFIL does not exist in the ARSLMFIL.

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-11 : 11:53:07

update AROPNFIL
set slspn_no = '001'
where NOT EXISTS(SELECT 1 FROM ARSLMFIL WHERE AROPNFIL.slspn_no = ARSLMFIL.slspn_no)

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-11 : 12:02:59

try this

Declare @a table ( sometext varchar(50))

insert into @a
select 'system' union all
select 'communication' union all
select 'electronics'

select * from @a

Declare @b table ( sometext varchar(50))

insert into @b
select 'system' union all
select 'sierra' union all
select 'tata'

select * from @b

update a1
set sometext = '001'
from @a a1
left join @b b1 on (a1.sometext = b1.sometext )
where b1.sometext is null

select * from @a

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-11 : 12:27:53

The first suggestion worked perfectly.

thanks alot

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-11 : 12:34:34

If you want a straight forward answer then try this

UPDATE A
SET A.slspn_no = '001'
FROM AROPNFIL A
LEFT JOIN ARSLMFIL AR ON ( AR.slspn_no = A.slspn_no )
WHERE AR.slspn_no IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources