kjky

原帖由 scf 於 2008-10-17 00:50 發表


其實係比人問起, 咁我想知答案
唔通我好似你個答案咁答咩

你應該係咁答喎, 無理由係錯既方法上做更錯的事!

scf

原帖由 henrywho 於 2008-10-17 18:12 發表
Assuming that you will keep one record only, and you have Oracle 10g or above,delete  from table1
where   rowid in
        (
        select  rrowid
        from    (
                select  rowid rrow ...

thanks , 不過如果係 SQL 2005 係唔係唔同寫法

icomefromhk

delete from mytable a where exist (
        select 1 from mytable b where a.name=b.name and
                (a.lastUpdateDate < b.lastUpdateDate or
                (a.lastUpdateDate = b.lastUpdateDate and a.id < b.id))
)

henrywho

原帖由 scf 於 2008-10-17 20:17 發表

thanks , 不過如果係 SQL 2005 係唔係唔同寫法

Yes, I think you can utilize analytic functions in SQL2005 to perform the same operation.  Sorry that I dunno M$ SQL.  ^__^

alphaau

其實最快同最徹底的做法, 應該係
select distinct * into #a from tableA
truncate tableA
insert into tableA
    select * from #a

咁就無晒duplicate data,

再執番program, 唔好俾佢入到duplicate data,

搞掂

bigmonster

一條過, 唔洗煩!!!
DELETE FROM table
WHERE rowid not in
(SELECT MIN(rowid)
FROM table
GROUP BY column1, column2, column3... ;

alphaau

原帖由 bigmonster 於 2008-10-20 10:37 發表
一條過, 唔洗煩!!!
DELETE FROM table
WHERE rowid not in
(SELECT MIN(rowid)
FROM table
GROUP BY column1, column2, column3... ;

人地想留新record 喎, 係唔係應該max(rowid)
不過...
人地個table 好似無rowid orz

rocketdive04

You can create a temp_table to store the most updated information > truncate the original one, and once u get the new information > insert to the original table

use test

create table temp_table
(
id int identity(1,1),
name nvarchar(50),
lastUpdateDate datetime
)

insert into temp_table
select distinct name,max(LastUpdateDate)as LastUpdate from org_table
group by name

truncate table org_table

insert into org_table
select * from temp_table
drop table temp_table

[ 本帖最後由 rocketdive04 於 2008-10-20 16:43 編輯 ]

jzu

咁得唔得

delete from TestTable
where id <> (
        select top 1 id
        from TestTable as inner
        where inner.name = TestTable.name
        order by [Last Update Date] desc
)

bigmonster

原帖由 alphaau 於 2008-10-20 10:59 發表

人地想留新record 喎, 係唔係應該max(rowid)
不過...
人地個table 好似無rowid orz

如果係oracle DB,  條條record 一定有 rowid , SQL Server 2005 都有同樣的field , 不過叫  Row_Number()

你有冇寫過DB program 架, 咁都唔知???

[ 本帖最後由 bigmonster 於 2008-10-22 09:13 編輯 ]