Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rtown
Yak Posting Veteran
53 Posts |
 Posted - 2011-01-06 : 12:16:18
|
| Hello, I am trying to figure out how to write an sql statement to do the following. When a user saves a record, one of the fields specifies a location in a rack, such as A1.So when the user saves a new record, how can I write it so it detects if there is already a value in that column that is A1, and deletes it, leaving only the new A1.Of course we dont want A1 for two products... I hope that makes sense. Im not sure how to proceed. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-01-06 : 12:19:36
|
| Use If Not Exists(select 1 from yourtable where locationinrack='a1') your Insert statementPBUH |
 |
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2011-01-06 : 12:51:42
|
quote:
Originally posted by Sachin.Nand
If Not Exists(select 1 from yourtable where locationinrack='a1') your Insert statement
Thanks for the reply. Im trying to figure out whats going on inside your sql statement. From what I can understand, this will only insert if A1 doesnt exists anywhere in the column... but what if it does?I want it to delete any other occurance of A1, and insert A1 in the row being edited. Can you break down your statement?  |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-01-06 : 12:54:20
|
| I'm not sure I fully understand your requiremnt, but one solution might be to:1. beging a transaction.2. Delete where exists3. Insert new value4. commit transationIf that doesn't work, then you might want to refine your question and post some DDL, DML and expected output. Here is a link that can help with that:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2011-01-06 : 13:11:38
|
quote:
Originally posted by Lamprey
I'm not sure I fully understand your requiremnt, but one solution might be to:1. beging a transaction.2. Delete where exists3. Insert new value4. commit transationIf that doesn't work, then you might want to refine your question and post some DDL, DML and expected output. Here is a link that can help with that:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Im really not sure how to make it any clearer... Location A1 should only exist once on the table, if it is inserted again, the old A1 field should be cleared.Is there such a thing as replace where exists? with null? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-01-06 : 14:01:20
|
quote:
Originally posted by rtown
Im really not sure how to make it any clearer... Location A1 should only exist once on the table, if it is inserted again, the old A1 field should be cleared.Is there such a thing as replace where exists? with null?
Firstly, It sounds like you need to constrain your data properly. But, maybe you already have taken care of that..? It's hard to help without seeing your data structures and data. Second, My solution matches the requirements you posted in your first post of deleting the existing row. Now you seem to be asking to null something out. So, what exactly is your requirement? Please, see the link I sent before on how to post a question. As I suggested, sample data and expected output go a long way towards demonstating what you want to do. |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2011-01-06 : 14:06:16
|
quote:
Originally posted by Lamprey
quote:
Originally posted by rtown
Im really not sure how to make it any clearer... Location A1 should only exist once on the table, if it is inserted again, the old A1 field should be cleared.Is there such a thing as replace where exists? with null?
Firstly, It sounds like you need to constrain your data properly. But, maybe you already have taken care of that..? It's hard to help without seeing your data structures and data. Second, My solution matches the requirements you posted in your first post of deleting the existing row. Now you seem to be asking to null something out. So, what exactly is your requirement? Please, see the link I sent before on how to post a question. As I suggested, sample data and expected output go a long way towards demonstating what you want to do.
Sorry for the confusion, i never wanted to delete the entire row, just any other A1 that exists. My sample data, is A1.... my expected output, is null. |
|
|
|
|
|
|
|
|
Detect duplicate value, and delete it