RE SIMPLE SQL STATEMENT
I cannot just delete the ct12 and keep the existing ct14 record instead of
deleting the later and updating the former because the former has some
columns with values that are useful which are not contained in the columns
of the later.Any way I have managed to write a program which can execute the
required action but with just one constraint. I need to copy a value in a
column of a second table which has more columns than the destination table.
eg)
table 1
regno amount linkref
34 89.00
table 2
regno amount docref proof linkref
34 89.00 7890 9 098765
So from the above tables I need to copy the value in the linkref column of
table 2 to the linkref
column of table 1. I cannot do a manual update of table 1 because the
records are over 10000, which could a tedious process prone to error. Can
assist with a simple SQL statement or 4gl program which can do this globaly
for all the affected rows.
-----Original Message-----
From: Art S. Kagel [mailto:kagel@bloomberg.net]
Sent: Thursday, November 30, 2000 8:03 PM
To: informix-list@iiug.org
Subject: Re: 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
Simbarashe.Pangeti@icl.com wrote:
>
> 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-----
> From: Mark D. Stock [mailto:mdstock@mydas.freeserve.co.uk]
> 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.
>
> Andrew Ford wrote:
> >
> > 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.
>
> +-------------------------------------------