RE: SIMPLE SQL STATEMENT
Second one in what, three days? Ok, I'll send it out to the whole world.
1 - you don't indicate how many rows are in the table. But it sounds like
you only want to delete 3000 or so, so I guess I shouldn't worry about.
2 - basic procedure is:
pull the data you want to keep out
delete all of the bad data
put the data you wanted to keep back in.
(we'll get to your exception to this in a minute)
to do that you:
select keys from tab group by keys having count(*) > 1 into temp t1
with no log;
update statistics for table t1; select tab.* from t1, tab where t1.keys=tab.keys into temp t2 with
no log;
*** delete from tab where exists (select 0 from t1 where
tab.keys=t1.keys);***
*** insert into tab select whatever you want to keep from t2;
*** depending on how many rows you want to delete and how big your table is
*** we'll go into the insert in a moment or two.
so - specific code - I don't know the name of your table, so I will continue
to call it tab. I assume the keys are name and regnomber.
select name, regnomber
from tab
group by 1,2
having count(*) > 1
into temp t1 with no log;
update statistics for table t1;
select tab.*
from t1, tab
where tab.name=t1.name
and tab.regnomber=t1.regnomber
into temp t2 with no log;
update statistics for table t2;
delete from tab
where exists (select 0 from t1
where t1.name=tab.name
and t1.regnomber=tab.regnomber);
{ now comes the question of what you want to keep from t2 and put back in
you want to keep the low recno, the name, the regnomber, and the highest
course?
ugh. }
-- let's get the recno at least
select name, regnomber, min(recno)
from t2
group by 1,2
into temp t3 with no log;
update statistics for table t3;
-- let's get the course now
select name, regnomber, max(course)
from t2
group by 1,2
into temp t4 with no log;
update statistics for table t4;
-- now we have the two halves in two separate tables, let's put them back
together.
insert into tab
select a.recno, a.name, a.regnomber, b.course
from t3 a, t4 b
where a.name=b.name
and a.regnomber=b.regnomber;
As always - make a copy of the table you're going to muck up before running
code you haven't tested.
If you're running this on a version 7 engine or higher, make sure you set
PDQPRIORITY to 5 or 10 - depends on row sizes, number of rows etc. If in
doubt just go for 100 and run it when nobody is looking. That's probably
way overkill.
hope that helps.
cheers
j.
> HELP
> I HAVE a table with duplicated records .this was a result
> of a aplication
> progmamme which inserted duplicates in stead of updating?
> i want to remove the duplicate and use some of the duplicate
> info to update
> ..
>
> eg
> recno name regnomber course
> 1 simba 345 ct12
> 2 john 356 ct13
> 3 simba 345 ct14
> 4 try 999 ct67
> 5 andy 444 ct66
> 6 john 356 ct78
>
> what i want to do
> 1 update record 1 with ct14
> 2 remove record 3
>
> ffor 3000 records so i want sql statement or a 4gl programme
> but i know this
> might abe a newbie question but ...bear with me i need this
> >