Assign unique IdentityNum to Date/Location only if same Date/Location Does NOT EXIST - SQL Server 2005

Hello Experts,

I am using the following code to assign a Date/Location to a unique IdentityNum:
 
update TableName
set
Date = @Date,
Location = @Location
Where IdentityNum IN(
select min(IdentityNum)
from TableName
where ISnull(Date,'') = '' and isnull(Location,'') = '')

This works great but I want to make sure that the same Date/Location can not be assigned more than once.

Does anyone know how to do this??

Thanks!  
SaxitalisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ErnariashCommented:

I hope this is what you need, it will not update if the same Date and Location exists on TableName
update TableName
set 
	Date = @Date,
	Location = @Location 
Where IdentityNum IN(
			select min(IdentityNum)
			from TableName
			where  ISnull(Date,'') = '' and isnull(Location,'') = ''
) 
AND not eXists ( select 1 from TableName t1
				where  t1.Date = TableName.Date and t1.Location = TableName.Location 
				AND	Date = @Date and Location = @Location 
)

Open in new window

0
SaxitalisAuthor Commented:
Thanks for the solution but it still updates. I don't quite see why you Select 1 from TableName?

Do I need to say something like:
 
AND not eXists ( select * from TableName
where Date = @Date and Location = @Location
)

Thanks!
0
ErnariashCommented:
Hello, You are missing some very important part of the subquery (t1=TableName)...It will only update the Date and Location  when @Date  @Location does not exists  in TableName
  t1.Date = TableName.Date and t1.Location = TableName.Location  
AND not eXists ( select 1 from TableName t1
where  t1.Date = TableName.Date and t1.Location = TableName.Location
AND Date = @Date and Location = @Location
)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

SaxitalisAuthor Commented:
OK - I'm not sure what I am missing here but...

1. I created a table called Tablename with 3 columns: IdentityNum int (prim key), Date datetime, Location varchar(50)
2. I then populated the IdentityNum for several records while leaving the other two columbns NULL
3. I then used your code and passed in @Date = '1/21/2008', @Location = 'Cascade'

See my code snippet. The table will update 1/21/2008 and cascade every time on the next record.

Can anyone see what is going on here??

Thanks!
update TableName
set 
	Date = '1/21/2008',
	Location = 'Cascade' 
Where IdentityNum IN(
			select min(IdentityNum)
			from TableName
			where  ISnull(Date,'') = '' and isnull(Location,'') = ''
) 
AND not eXists ( select IdentityNum from TableName t1
				where  t1.Date = TableName.Date and t1.Location = TableName.Location 
				AND	Date = '1/21/2008' and Location = 'Cascade'  

Open in new window

0
ErnariashCommented:
Is date a datetime Date?
0
ErnariashCommented:

If you confirm the dates on your tables do not includes min sec, then try the query..you also could try to add a constrain to your table

update TableName
set 
	Date = '1/21/2008',
	Location = 'Cascade' 
from 	TableName
Where IdentityNum IN(
			select min(IdentityNum)
			from  TableName
			where  ISnull(TableName.Date,'') = '' and isnull(TableName.Location,'') = ''
) and 
 not eXists ( select 1 from TableName t1
				where  t1.Date = '1/21/2008' and t1.Location = 'Cascade'  
)

Open in new window

0
SaxitalisAuthor Commented:
Yeah you are right since it is a datetime...

I changed the Date param value to Date = '1/21/2008 12:00:00 AM' in order to exactly match the value already in the table but it still updates.

It is still updating the same values

Any other ideas??
Thanks!
update TableName
set 
	Date = '1/21/2008 12:00:00 AM',
	Location = 'Cascade' 
Where IdentityNum IN(
			select min(IdentityNum)
			from TableName
			where  ISnull(Date,'') = '' and isnull(Location,'') = ''
) 
AND not eXists ( select IdentityNum from TableName t1
				where  t1.Date = TableName.Date and t1.Location = TableName.Location 
				AND	Date = '1/21/2008 12:00:00 AM' and Location = 'Cascade'  
)

Open in new window

0
ErnariashCommented:
:) sorry about the where
AND not eXists ( select IdentityNum from TableName t1
where  Date = '1/21/2008 12:00:00 AM' and Location = 'Cascade'  
)
 
see my last post, it will work....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SaxitalisAuthor Commented:
Very Good! Thank you sir!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.