check if record exists in another table

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 2005 Forums

Transact-SQL (2005)

check if record exists in another table

Author

Topic

magikminox
Starting Member

27 Posts

Posted - 2008-07-22 : 07:44:35

Hi
How do i check if a certian row exists in another table from another table.How do i also make sure my query does not fail when that record does not exist.
ie if record is found,continue and update, but if record does not exist display message or do nothing but avoid query from failing.

if exists (select name from bullion..sysusers where name= suser_name())
begin
declare @bulFlag bit
select @bulFlag = (select reset_flag from bullion.dbo.tbl_password_reset_flag
where username = suser_name())
if(@flag <> @bulFlag)
begin
update bullion.dbo.tbl_password_reset_flag
set reset_flag = @flag
where username = suser_name()
end
end
else
begin
select 'User, ' suser_name() + ', does not exist on database:bullion '
end

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-22 : 07:51:08

You can use raiseerror when the condition fails.Something like
raiserror('your message',16,1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 08:00:10

and use left join or not in to check if records exists in other table.

select fields
from tablea
left join tableb
on tablea.pk=tableb.fk
where tableb.fk is null

will give you tablea records not in tableb

magikminox
Starting Member

27 Posts

Posted - 2008-07-22 : 08:09:48

What if i want the query to still continue even if it fails.Will raiserror() stop the query executing.How can i ignore the error.

Thnks a lot guys

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-22 : 08:12:58

Yes it will stop it.

magikminox
Starting Member

27 Posts

Posted - 2008-07-22 : 08:21:56

how do i make the query to ignore the fact that record does not exist.c
hecking is bit too complex and i'd rather just ignore the error.

cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 08:22:46

quote:
Originally posted by magikminox

how do i make the query to ignore the fact that record does not exist.c
hecking is bit too complex and i'd rather just ignore the error.

cheers

and you want insert to happen?

magikminox
Starting Member

27 Posts

Posted - 2008-07-22 : 08:34:27

my query checks if row exists in 2 tables.if it does exist in one it should insert and if it doesnt on the other no insert can occur but the query must not be interupted.

in other words if row dows not exist in other table its OK but dont stop the other table being inserted into.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-22 : 08:36:33

Maybe an instead trigger will be a better option.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 09:54:17

quote:
Originally posted by magikminox

my query checks if row exists in 2 tables.if it does exist in one it should insert and if it doesnt on the other no insert can occur but the query must not be interupted.

in other words if row dows not exist in other table its OK but dont stop the other table being inserted into.

sorry i didnt get you. Did you mean inserting consecutively in two tables? can you explain with data sample please?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources