Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Check for potential duplicates before updating

 
Fred Victa
Ranch Hand
Posts: 190
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Suppose I have a column called X and it has a certain value in it. How would I write some SQL statements that would check to see if an update statement would insert a duplicate value into column X?
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dont check duplicates manually.
You can add unique constraint and let the database check it.
And you can handle exception when database throws it for duplicate value.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you don't want duplicate values in a given column, you should add a unique constraint to this column. And then your update statement will fail if a value is updated to a duplicate one
 
Fred Victa
Ranch Hand
Posts: 190
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for the suggestions. That column needs a unique constraint if other columns have certain values. How can this be set up?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fred Victa wrote:That column needs a unique constraint if other columns have certain values. How can this be set up?

Why didn't you post this in your first post because that's very important information Do you need a unique combination of column values? Or do you want the uniqueness only if another column has a specific value?
 
Fred Victa
Ranch Hand
Posts: 190
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Fred Victa wrote:That column needs a unique constraint if other columns have certain values. How can this be set up?

Why didn't you post this in your first post because that's very important information Do you need a unique combination of column values? Or do you want the uniqueness only if another column has a specific value?


I want the uniqueness only if another column has a specific value. For example, suppose column A has a 2. Column B has integers, but I don't want there to be duplicates if column A has a 2.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fred Victa wrote:I want the uniqueness only if another column has a specific value. For example, suppose column A has a 2. Column B has integers, but I don't want there to be duplicates if column A has a 2.

That's a pretty weird requirement... Let me see if I understand this requirement correctly.

Column B has a bunch of integers with duplicates, e.g. 5 rows have value 1979. So inserting a row with colA=2 and colB=1979 should fail. And setting colA=2 for a row where colB=1979 should fail as well. But I can insert a new record with colA=3 and colB=1979 without any problem. Correct?
 
Fred Victa
Ranch Hand
Posts: 190
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Fred Victa wrote:I want the uniqueness only if another column has a specific value. For example, suppose column A has a 2. Column B has integers, but I don't want there to be duplicates if column A has a 2.

That's a pretty weird requirement... Let me see if I understand this requirement correctly.

Column B has a bunch of integers with duplicates, e.g. 5 rows have value 1979. So inserting a row with colA=2 and colB=1979 should fail. And setting colA=2 for a row where colB=1979 should fail as well. But I can insert a new record with colA=3 and colB=1979 without any problem. Correct?


Yes, that is correct.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's probably impossible to solve with a simple unique index/constraint on that column. Although it depends on the database you are using.

I know for SQL Server 2008 (and later versions) you can create a filtered indexMaybe something similar can be done for other databases.

Another possibility could be to create an updatable and/or insertable view with only the records where ColA is equal to 2 and add (if possible) a unique index on ColB of the view.

Hope it helps!
Kind regards,
Roel
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic