查看: 1374|回复: 3
|
SQL Query On Duplicate Key 求教。
[复制链接]
|
|
以下是我用来delete duplicated Key的query. 但是它delete的是original records.
1。要如何把original records 留着,只是把不要的删除?
2。可以把这个query run的更加快吗?
SET NOCOUNT ON
SET ROWCOUNT 1
WHILE 1 = 1
BEGIN
DELETE FROM trs_billdetails
WHERE trsno IN (
select trsno
from trs_billdetails
where trsno like ('V004%')
group by trsno, billid,itemcode,plucode,basedqty,unitqty,unitprice
having count(trsno) > 1)
IF @@Rowcount = 0
BREAK ;
END
SET ROWCOUNT 0
|
|
|
|
|
|
|
|
发表于 13-9-2012 01:11 AM
|
显示全部楼层
DELETE FROM trs_billdetails a
WHERE rowid IN (
select max(rowid)
from trs_billdetails b
where trsno like ('V004%')
group by trsno
having count(trsno) > 1)
|
|
|
|
|
|
|
|
发表于 14-9-2012 05:00 PM
|
显示全部楼层
DELETE t1 FROM trs_billdetails t1, trs_billdetails t2 WHERE t1.id > t2.id AND t2.name LIKE ('V004%')
|
|
|
|
|
|
|
|
发表于 26-9-2012 10:33 PM
|
显示全部楼层
可以说明一下你的original record特征是什么吗?? |
|
|
|
|
|
|
| |
本周最热论坛帖子
|