• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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!  
0
Saxitalis
Asked:
Saxitalis
  • 5
  • 4
1 Solution
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
 
SaxitalisAuthor Commented:
Very Good! Thank you sir!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now