How do you check that a value you want to add is n

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

How do you check that a value you want to add is n

Author

Topic

Maz
Starting Member

1 Post

Posted - 2010-09-24 : 11:03:22

Hi,

How do you check that a value you want to add is not in the table?

I have written the following

insert into activitytype(activitytypeseqno, activitytype, systemind, status, usid, tmstamp) select activitytypeseq.nextval, 'testactivitytype',5,1,'ACTOME01',sysdate
from activitytype
where not exists (select * from activitytype where activitytype = 'testactivitytype');

insert into activitytype(activitytypeseqno, activitytype, systemind, status, usid, tmstamp) select
activitytypeseq.nextval, 'test5',5,1,'ACTOME01',sysdate
from activitytype
where not exists (select * from activitytype where activitytype = 'test5');

"testactivitytype" exists in the table, and it ignores this, but when it tries to add "test5" to the table it returns a unique identifier value error with regards to "test5" even though it's unique?

Cheers, maz

MAz

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-24 : 11:42:39

Try moiving the NOT EXISTS clause to an IF statement:

if (not exists (select * from activitytype where activitytype = 'testactivitytype'))
begin
	insert activitytype
	(
		activitytypeseqno, 
		activitytype, 
		systemind, 
		status, 
		usid, 
		tmstamp
	) 
	select 
		activitytypeseq.
		nextval, 
		'testactivitytype',
		5,
		1,
		'ACTOME01',
		sysdate 
	from 
		activitytype
end

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 12:56:52

Lampy...I don't think so....

You need to think about the key to the column...what is testactivitytype already exists for a key?

[code]
INSERT INTO activitytype
(activitytypeseqno , activitytype , systemind , status, usid , tmstamp)
SELECT nextval , 'testactivitytype', 5 , 1 ,'ACTOME01' , sysdate
FROM activitytype o
WHERE NOT EXISTS (
SELECT *
FROM activitytype i
WHERE i.key = o.key
AND activitytype = 'testactivitytype');
[/code[

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-24 : 13:31:02

Yeah, could be. Hard to tell from the description. If "Test5" is unique is activitytypeseq.nextval causing the error? What error is being returned?

Check out Brett's link, it might be helpful in getting an answer:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources