简单的插入过程，检查重复
我正在创建一个程序，它将数据插入到一个非常简单的表中，
但我的问题是我希望我的插入语句确保它不会插入重复的数据
我想以某种方式检查数据要插入的表确保没有一行具有相同的 indivualid、categoryid 和 value
因此，如果我正在插入
indivualid = 1
categorid = 1
value = 1
一行包含我的数据的行
indivualid = 1
categorid = 1
value = 2
，并且在我的表中，仍然会插入
，但如果有一行，
indivualid = 1
categorid = 1
value = 1
那么我不会
尝试这样做
IF @value = 'Y'
OR @value = 'A'
OR @value = 'P'
AND NOT EXISTS
(SELECT categoryid,
individualid
FROM ualhistory
WHERE categoryid = @cat
AND individualid = @id)
INSERT INTO individuory(categoryid, individualid, value, ts)
VALUES (@cat,
@id,
@yesorno,
getdate())
，但它仍然插入重复项。
I am creating a program that is going to insert data into a table which is pretty simple
But my issue is I want my insert statement to make sure that it isnt inserting duplicate data
I want to somehow check the table the data is going into to make sure that there isnt a row with the same indivualid and categoryid and value
So if I am inserting
indivualid = 1
categorid = 1
value = 1
and in my table there is a row with
indivualid = 1
categorid = 1
value = 2
my data would still be inserted
but if there was a row with
indivualid = 1
categorid = 1
value = 1
then it wouldnt
I tried this
IF @value = 'Y'
OR @value = 'A'
OR @value = 'P'
AND NOT EXISTS
(SELECT categoryid,
individualid
FROM ualhistory
WHERE categoryid = @cat
AND individualid = @id)
INSERT INTO individuory(categoryid, individualid, value, ts)
VALUES (@cat,
@id,
@yesorno,
getdate())
but it still inserts duplicates.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以通过以下方式执行此操作：
现在，您的方法的确切问题是您没有关联 OR，因此条件变为 true 并且始终插入数据。您可以将您的陈述更改为：
而且我认为它也会起作用。
You can do that in the following manner:
Now, the exact problem with your approach is that you are not associating the ORs and therefore, the condition becomes true and always inserts the data. You can change your statement to this:
And I think it will work also.
在 (individualid、categoryid、value) 上添加
UNIQUE约束，服务器将不允许您插入重复行。http://msdn.microsoft.com/en-us/library/ms189862.aspx
Add a
UNIQUEconstraint on (individualid, categoryid, value) and the server won't let you insert a duplicate row.http://msdn.microsoft.com/en-us/library/ms189862.aspx