Re: How to update a huge table in smaller chunks
On Aug 22, 10:46 am, Veeru71 <m_ad...@hotmail.com> wrote:
> Is there any command in Informix that is equivalent to "SET ROWCOUNT"
> of Sybase/MS Sql Server ?
>
> Basically I am trying to update a column in a huge table (50 million
> rows) without a WHERE clause (need to set a flag in all the rows).
>
> In Sybase, I can do "SET ROWCOUNT 1000" and write a small WHILE loop
> as below.....
>
> SET ROWCOUNT 1000;
> WHILE << some condition>>
> UPDATE <table> SET flag = 'Y';
> commit;
> END WHILE;
>
> Effectively, this updates 1000 rows at a time so that we will not run
> into transaction log fillup issues.
> What is the best way of doing this in Informix ?
> Thanks in advance.
CREATE TEMP TABLE governor( counter serial, row_id int );
INSERT INTO governor
SELECT 0, rowid from <table> WHERE flag != 'Y';
SELECT MAX(counter) INTO max_cnt FROM governor;
LET ix = 1;
WHILE ix < max_cnt
BEGIN WORK;
UPDATE <table> SET flag = 'Y' WHERE rowid in (SELECT row_id FROM
governor WHERE counter < (1000 * ix) );
LET ix = ix + 1;
COMMIT WORK;
END WHILE
DROP TABLE governor;
Art S. Kagel
On Aug 22, 11:24 am, "Art S. Kagel" <art.ka...@gmail.com> wrote:
> On Aug 22, 10:46 am, Veeru71 <m_ad...@hotmail.com> wrote:
>
>
>
> > Is there any command in Informix that is equivalent to "SET ROWCOUNT"
> > of Sybase/MS Sql Server ?
>
> > Basically I am trying to update a column in a huge table (50 million
> > rows) without a WHERE clause (need to set a flag in all the rows).
>
> > In Sybase, I can do "SET ROWCOUNT 1000" and write a small WHILE loop
> > as below.....
>
> > SET ROWCOUNT 1000;
> > WHILE << some condition>>
> > UPDATE <table> SET flag = 'Y';
> > commit;
> > END WHILE;
>
> > Effectively, this updates 1000 rows at a time so that we will not run
> > into transaction log fillup issues.
> > What is the best way of doing this in Informix ?
> > Thanks in advance.
>
> CREATE TEMP TABLE governor( counter serial, row_id int );
> INSERT INTO governor
> SELECT 0, rowid from <table> WHERE flag != 'Y';>
> SELECT MAX(counter) INTO max_cnt FROM governor;
>
> LET ix = 1;
> WHILE ix < max_cnt
> BEGIN WORK;
> UPDATE <table> SET flag = 'Y' WHERE rowid in (SELECT row_id FROM
> governor WHERE counter < (1000 * ix) );
> LET ix = ix + 1;
> COMMIT WORK;
> END WHILE
>
> DROP TABLE governor;>
> Art S. Kagel
Note: the WHILE condition should be:
max_ix = (max_cnt + 999) / 1000
WHILE ix <= max_ix
...
Art S. Kagel