Update command
All, I am trying to run an update command a little at a time. In oracle you can do it by ronum as follows: update tablea set yyy = 'zzz' where yyy = 'zzzz' and rownum <5000; I would like to do the same in informix v10 on HPUX 11.23. I am still very green with Informix and would appreciate some assistance with the command. I can't do one bulk update as we do not have enough logical logs to support the command. The tables are over 50 million in row size. Please Advise Thanks
There is no such facility. You could do the equivalent with a host language using a cursor. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Apr 21, 2009 at 12:05 PM, JOHN CAPPELLANO < john.cappellano@tycoelectronics.com> wrote: > All, > I am trying to run an update command a little at a time. In oracle you can > do > it by ronum as follows: update tablea set yyy = 'zzz' where yyy = 'zzzz' > and > rownum <5000; > > I would like to do the same in informix v10 on HPUX 11.23. I am still very > green with Informix and would appreciate some assistance with the command. > I > can't do one bulk update as we do not have enough logical logs to support > the > command. The tables are over 50 million in row size. > > Please Advise > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b52b484f0504681405f2
Well, you "shouldn't" but
I'm on IDS 10.00.FC6 on HP-UX B.11.11
And I just selected rowid from a simple table.
I am rusty on rowid because that has not been an acceptable practice
for... oh gosh at least 10+ years...but if the OP's table is not
fragmented, couldn't he use rowid? I know.. he "shouldn't"...
John,
run a "dbschema -ss -t tablename -d databasename" from the unix command
line (as Informix unless your env is set)... that will show you your
indices... perhaps you can do your update using an index in your where
clause if you have indices that will segment your data sufficiently.
NJ
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Tuesday, April 21, 2009 12:32 PM
To: ids@iiug.org
Subject: Re: Update command [15571]
There is no such facility.
You could do the equivalent with a host language using a cursor.
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
On Tue, Apr 21, 2009 at 12:05 PM, JOHN CAPPELLANO <
john.cappellano@tycoelectronics.com> wrote:
> All,
> I am trying to run an update command a little at a time. In oracle you
can
> do
> it by ronum as follows: update tablea set yyy = 'zzz' where yyy =
'zzzz'
> and
> rownum <5000;
>
> I would like to do the same in informix v10 on HPUX 11.23. I am still
very
> green with Informix and would appreciate some assistance with the
command.
> I
> can't do one bulk update as we do not have enough logical logs to
support
> the
> command. The tables are over 50 million in row size.
>
> Please Advise
>
> Thanks
>
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5b52b484f0504681405f2
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
If you try to delete all the rows in the table why don't you use truncate table-name OTOH if you want the update route then try to use rowid instead of rownum, it's not the same but it might help. J. 2009/4/21 JOHN CAPPELLANO <john.cappellano@tycoelectronics.com> > All, > I am trying to run an update command a little at a time. In oracle you can > do > it by ronum as follows: update tablea set yyy = 'zzz' where yyy = 'zzzz' > and > rownum <5000; > > I would like to do the same in informix v10 on HPUX 11.23. I am still very > green with Informix and would appreciate some assistance with the command. > I > can't do one bulk update as we do not have enough logical logs to support > the > command. The tables are over 50 million in row size. > > Please Advise > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd4d8e60bf6ca046815e2c8
Yeah, not the same and not recommended. The problem with using ROWID to
limit an UPDATE is that you have no knowledge of what the valid rowid values
are for a table! You would have to first query the table to get the list of
rowids then somehow produce a reasonable sub-list. Remember that only N
rows fit on a page, so you can't just find the lowest rowid and update the
next 1500 rowids because that may only update 6 rows if only one row fits on
a page!
ROWID and Oracle's ROWNUM (which is the sequential ordinal row within a
selected set of rows) are VERY different.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Apr 21, 2009 at 3:21 PM, Norma Jean Sebastian <
nsebastian@flinnsci.com> wrote:
> Well, you "shouldn't" but
> I'm on IDS 10.00.FC6 on HP-UX B.11.11
> And I just selected rowid from a simple table.
> I am rusty on rowid because that has not been an acceptable practice
> for... oh gosh at least 10+ years...but if the OP's table is not
> fragmented, couldn't he use rowid? I know.. he "shouldn't"...
>
> John,
> run a "dbschema -ss -t tablename -d databasename" from the unix command
> line (as Informix unless your env is set)... that will show you your
> indices... perhaps you can do your update using an index in your where
> clause if you have indices that will segment your data sufficiently.
>
> NJ
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Tuesday, April 21, 2009 12:32 PM
> To: ids@iiug.org
> Subject: Re: Update command [15571]
>
> There is no such facility.
> You could do the equivalent with a host language using a cursor.
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> On Tue, Apr 21, 2009 at 12:05 PM, JOHN CAPPELLANO <
> john.cappellano@tycoelectronics.com> wrote:
>
> > All,
> > I am trying to run an update command a little at a time. In oracle you
> can
> > do
> > it by ronum as follows: update tablea set yyy = 'zzz' where yyy =
> 'zzzz'
> > and
> > rownum <5000;
> >
> > I would like to do the same in informix v10 on HPUX 11.23. I am still
> very
> > green with Informix and would appreciate some assistance with the
> command.
> > I
> > can't do one bulk update as we do not have enough logical logs to
> support
> > the
> > command. The tables are over 50 million in row size.
> >
> > Please Advise
> >
> > Thanks
> >
> >
> >
> >
> ************************************************************************
> *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001636c5b52b484f0504681405f2
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5b6218f0665046816ada9