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

Saxitalis
Saxitalis used Ask the Experts™
on
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!  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

Author

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!
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
)
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Author

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

Is date a datetime Date?

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

Author

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

:) 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....

Author

Commented:
Very Good! Thank you sir!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial