SIMPLE SQL STATEMENT 
Author Message
 SIMPLE SQL STATEMENT

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

Quote:
-----Original Message-----

Sent: Wednesday, November 22, 2000 1:50 AM
To: Andrew Ford

Cc: Informix-List (E-mail)
Subject: Re: You've heard it all before, cont.


> I'm posting some other onstat commands that may help.

> Informix Dynamic Server Version 7.31.FC4    -- On-Line -- Up 3 days
09:44:13 -- 2545024 Kbytes

> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 261671478 74154240 9591835411 97.27   55502789 51443264 212025414 73.82

> isamtot  open     start    read     write    rewrite  delete   commit
rollbk
> 8587085144 43126935 172567752 4682012886 56281592 10069964 3147447
22054673 42895

> gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
> 0        0        0        0        0        0        0

> ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
> 0        0            1187     151984.04 65843.13 967      1934

> bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
> 16758474 48898    5081207577 0        0        18077    2029819  642171

> ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
> 92314484 3084372  1919689  97237545   3309121

Definitely short of buffers. I would also guess that your LRU parameters
are too low, unless they were lowered to improve checkpoint times.

> Sorry about that, UPDATE STATS was run (I even used Art's program to do
it).

That's okay then.

> Another thing I would like to add.

> VP Scheduler Statistics:
>  vp    pid       class       semops    busy waits  spins/wait
>  1     28505     cpu         17491     17534       1000
>  2     28506     adm         0         0           0
>  3     28507     cpu         20517     20586       999
>  4     28508     cpu         19405     19467       999
>  5     28509     cpu         18285     18334       999
>  6     28510     cpu         69192125  69325060    999
>  7     28511     cpu         56643372  56765184    999
>  8     28512     cpu         44975001  45084655    999
>  9     28513     lio         2         0           0
>  10    28514     pio         2         0           0
>  11    28515     aio         146       0           0
>  12    28516     msc         30523     0           0

> There are a lot of semops and busy waits on VP's 6,7,8.  Is this ok
> since the spins/wait are at or near 1000?

> MT global info:
> sessions threads  vps      lngspins
> 114      324      12       434

>           sched calls     thread switches yield 0   yield n   yield
forever
> total:    1913107135      1490724804      426169445 16877225  694531053
> per sec:  0               0               0         0         0

> Virtual processor summary:
>  class       vps       usercpu   syscpu    total
>  cpu         7         151956.74  65772.65  217729.39
>  aio         1         2.74      12.17     14.91
>  lio         1         2.12      7.70      9.82
>  pio         1         2.09      7.85      9.94
>  adm         1         14.27     34.33     48.60
>  msc         1         6.08      8.43      14.51
>  total       12        151984.04  65843.13  217827.17

> Individual virtual processors:
>  vp    pid       class       usercpu   syscpu    total
>  1     28505     cpu         27636.92  18176.92  45813.84
>  2     28506     adm         14.27     34.33     48.60
>  3     28507     cpu         25982.39  16773.92  42756.31
>  4     28508     cpu         30565.32  17020.38  47585.70
>  5     28509     cpu         16064.93  3393.09   19458.02
>  6     28510     cpu         20905.27  4161.71   25066.98
>  7     28511     cpu         17043.99  3418.54   20462.53
>  8     28512     cpu         13757.92  2828.09   16586.01
>  9     28513     lio         2.12      7.70      9.82
>  10    28514     pio         2.09      7.85      9.94
>  11    28515     aio         2.74      12.17     14.91
>  12    28516     msc         6.08      8.43      14.51
>                  tot         151984.04  65843.13  217827.17

Is this a DB server only or are you running other apps on this box? If
you are, then I would remove about 3 CPU VPs for starters. You would
have to alter corresponding parameters like NETTYPE.

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+

| http://www.***.com/   http://www.***.com/ |/////  / //|
| http://www.***.com/  +-----------------------------------+////  / ///|
|                      |This email will self-destruct in   |///  / ////|
|                      |10 sec. If you received this email |//  / /////|
|                      |in error, sorry about the mess.    |/  ////////|
+----------------------+-----------------------------------+-----------+



Wed, 18 Jun 1902 08:00:00 GMT
 SIMPLE SQL STATEMENT

Quote:

> 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

Your requirement is not clear : What is the business rule that demands that
recno =1 should be updated to ct14? Is it that 'ct14' is greater than 'ct12' or
is it that the recno is higher and therefore the information is more current?

What about recno 2 and 6? Does 2 have to be updated to ct78?

Why does recno 1 have to be updated to ct14? Can't it just be deleted, leaving
recno 3 as is to represent simba's selection?

Rudy



Wed, 18 Jun 1902 08:00:00 GMT
 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.

Quote:
> 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



Wed, 18 Jun 1902 08:00:00 GMT
 SIMPLE SQL STATEMENT
The real problem is that this table should have a PRIMARY KEY constraint,
a UNIQUE constraint, or AT LEAST a UNIQUE index on regnomber to prevent the
insertion of duplicate rows.  There is of course no easy way to do this.  
You will have to manually perform the deletes and updates.  (BTW why not
just delete the ctl2 record and keep the existing ctl4 record instead of
deleting the latter and updating the former?)  Finding the dups is easy:

SELECT name, regnomber, COUNT(*)
FROM mytable
GROUP BY 1,2
HAVING COUNT(*) > 1
ORDER BY 2;

If you have ISQL create a quick form and use that to perform the deletes
and updates once you have the list of regnombers that are duplicated.

Art S. Kagel

Quote:

> 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

> -----Original Message-----

> Sent: Wednesday, November 22, 2000 1:50 AM
> To: Andrew Ford
> Cc: Informix-List (E-mail)
> Subject: Re: You've heard it all before, cont.


> > I'm posting some other onstat commands that may help.

> > Informix Dynamic Server Version 7.31.FC4    -- On-Line -- Up 3 days
> 09:44:13 -- 2545024 Kbytes

> > Profile
> > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> > 261671478 74154240 9591835411 97.27   55502789 51443264 212025414 73.82

> > isamtot  open     start    read     write    rewrite  delete   commit
> rollbk
> > 8587085144 43126935 172567752 4682012886 56281592 10069964 3147447
> 22054673 42895

> > gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
> > 0        0        0        0        0        0        0

> > ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
> > 0        0            1187     151984.04 65843.13 967      1934

> > bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
> > 16758474 48898    5081207577 0        0        18077    2029819  642171

> > ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
> > 92314484 3084372  1919689  97237545   3309121

> Definitely short of buffers. I would also guess that your LRU parameters
> are too low, unless they were lowered to improve checkpoint times.

> > Sorry about that, UPDATE STATS was run (I even used Art's program to do
> it).

> That's okay then.

> > Another thing I would like to add.

> > VP Scheduler Statistics:
> >  vp    pid       class       semops    busy waits  spins/wait
> >  1     28505     cpu         17491     17534       1000
> >  2     28506     adm         0         0           0
> >  3     28507     cpu         20517     20586       999
> >  4     28508     cpu         19405     19467       999
> >  5     28509     cpu         18285     18334       999
> >  6     28510     cpu         69192125  69325060    999
> >  7     28511     cpu         56643372  56765184    999
> >  8     28512     cpu         44975001  45084655    999
> >  9     28513     lio         2         0           0
> >  10    28514     pio         2         0           0
> >  11    28515     aio         146       0           0
> >  12    28516     msc         30523     0           0

> > There are a lot of semops and busy waits on VP's 6,7,8.  Is this ok
> > since the spins/wait are at or near 1000?

> > MT global info:
> > sessions threads  vps      lngspins
> > 114      324      12       434

> >           sched calls     thread switches yield 0   yield n   yield
> forever
> > total:    1913107135      1490724804      426169445 16877225  694531053
> > per sec:  0               0               0         0         0

> > Virtual processor summary:
> >  class       vps       usercpu   syscpu    total
> >  cpu         7         151956.74  65772.65  217729.39
> >  aio         1         2.74      12.17     14.91
> >  lio         1         2.12      7.70      9.82
> >  pio         1         2.09      7.85      9.94
> >  adm         1         14.27     34.33     48.60
> >  msc         1         6.08      8.43      14.51
> >  total       12        151984.04  65843.13  217827.17

> > Individual virtual processors:
> >  vp    pid       class       usercpu   syscpu    total
> >  1     28505     cpu         27636.92  18176.92  45813.84
> >  2     28506     adm         14.27     34.33     48.60
> >  3     28507     cpu         25982.39  16773.92  42756.31
> >  4     28508     cpu         30565.32  17020.38  47585.70
> >  5     28509     cpu         16064.93  3393.09   19458.02
> >  6     28510     cpu         20905.27  4161.71   25066.98
> >  7     28511     cpu         17043.99  3418.54   20462.53
> >  8     28512     cpu         13757.92  2828.09   16586.01
> >  9     28513     lio         2.12      7.70      9.82
> >  10    28514     pio         2.09      7.85      9.94
> >  11    28515     aio         2.74      12.17     14.91
> >  12    28516     msc         6.08      8.43      14.51
> >                  tot         151984.04  65843.13  217827.17

> Is this a DB server only or are you running other apps on this box? If
> you are, then I would remove about 3 CPU VPs for starters. You would
> have to alter corresponding parameters like NETTYPE.

> Cheers,
> --
> Mark.

> +----------------------------------------------------------+-----------+

> | http://www.informix.com  http://www.informixhandbook.com |/////  / //|
> | http://www.iiug.org  +-----------------------------------+////  / ///|
> |                      |This email will self-destruct in   |///  / ////|
> |                      |10 sec. If you received this email |//  / /////|
> |                      |in error, sorry about the mess.    |/  ////////|
> +----------------------+-----------------------------------+-----------+



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Simple SQL Statement - unique combinations

2. Help with simple SQL statement

3. Newbie: Help needed with simple SQL Statement

4. problems with simple SQL statement

5. Newbie question... Probably very simple SQL Statement :)

6. Simple SQL statement (Insert entry if entry doesnt exist)

7. Simple SQL statement (2 - tables, 3 - Fields)

8. Simple SQL-statement wanted!

9. help with simple sql statement

10. problem with simple sql statement

11. Simple SQL Statement

12. Help with a simple SQL statement


 
Powered by phpBB® Forum Software