RE: Update daily one row of a while table

Jacques If you need to do this as a single transaction you have a problem! If you can do it as many smaller transactions there are two alternatives. 1. Write a 4GL program using a cursor to select and update each row of the table individually. 2. Identify a second column in the table (serial or similar) that has a widespread but known range of values. Select limited values within this range (about 20000 records at a time) and update this selection. Option 2 will be quicker but will have impact on other people accessing the table, option 1 will be slow (indeed it may not complete within 24 hours depending on the quantity of data and the machine !!) but sure but will have minimal impact. Good Luck, I wouldn't like this problem!! :-) Keith -> -----Original Message----- -> From: Jacques SANSON [mailto:jacques.sanson@ferma.fr] -> Sent: Tuesday, April 20, 2004 11:01 AM -> To: Simmons, Keith -> Cc: informix-list@iiug.org -> Subject: RE: Update daily one row of a while table -> -> -> Keith, -> -> indeed my english is a bit rusty and i meant -> 1 column on the entire table. -> -> Indeed the (not my) design is not really great -> but I have to make with it. -> -> So the question : -> Is there an efficient way to update 1 column -> on one entire large table ? The design cannot be -> changed easily. -> -> --Jacques-- -> -> -----Message d'origine----- -> De : Simmons, Keith [mailto:keith.simmons@office2office.biz] -> Envoy' : mardi 20 avril 2004 11:44 -> ' : 'jacques.sanson@ferma.fr' -> Cc : 'informix-list@iiug.org' -> Objet : RE: Update daily one row of a while table -> -> -> Jacques -> -> I am confused? You want to update a single row on a table, -> Yes. If so you -> only require 1 lock for the data row and 1 lock for each -> index. Do you mean -> you want to update one column on the entire table? If so you -> need to revisit -> you design !! :-) -> What exactly is the error you are encountering. -> -> Keith -> -> -> -----Original Message----- -> -> From: jacques.sanson@ferma.fr [mailto:jacques.sanson@ferma.fr] -> -> Sent: Tuesday, April 20, 2004 8:03 AM -> -> To: informix-list@iiug.org -> -> Subject: Update daily one row of a while table -> -> -> -> -> -> Hi, -> -> -> -> I need to update one row of a whole table. -> -> The row is a daily counter, so it needs to be reset -> -> everyday. -> -> -> -> I need to do this on a very large table (1 to 10 million rows), -> -> which exceeds the maximum number of locks available, -> -> so a simple UPDATE statement will not do. -> -> -> -> A table lock will not do either, since this table needs -> -> to be accessible while this update is performed. -> -> -> -> Is there a ready solution for this (either in SQL or ESQL-C ?) -> -> with IDS 9.30.UC2 or above ? -> -> -> -> Thanks, -> -> --Jacques-- -> -> -> -> -> ************************************************************* -> *************** -> ****** -> This message is sent in strict confidence for the addressee -> only. It may -> contain legally privileged information. The contents are not -> to be disclosed -> to anyone other than the addressee. Unauthorised recipients -> are requested -> to preserve this confidentiality and to advise the sender -> immediately of any -> error in transmission. -> This footnote also confirms that this email message has been -> swept for the -> presence of computer viruses, however we cannot guarantee -> that this message -> is free from such problems. -> ************************************************************* -> *************** -> ****** -> -> sending to informix-list