Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-12
9
Medium Priority
?
231 Views
Last Modified: 2012-06-27
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
Comment
Question by:Saxitalis
  • 5
  • 4
9 Comments
 
LVL 9

Expert Comment

by:Ernariash
ID: 22942258

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
 

Author Comment

by:Saxitalis
ID: 22947616
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22950759
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:Saxitalis
ID: 22955629
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22955667
Is date a datetime Date?
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22955857

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
 

Author Comment

by:Saxitalis
ID: 22955968
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
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22956089
:) 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
 

Author Closing Comment

by:Saxitalis
ID: 31516024
Very Good! Thank you sir!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question