RE: select in update

In XPS you can always use the UPDATE JOIN update tab1 set tab1.col1 = tab2.col1, tab1.col2=tab2.col2..... from tab1, tab2 where tab1.key = tab2.key It rocks. Imagine doing 40 million updates to a billion row table in 30 minutes with no indexes. There is also a delete join which also rocks - although not to the same degree. To get back to the orignal question - you could also toss in a CASE statement to take specific LITERAL action (when 'a' then 'b'). If that's not possible, then I would be prone to solve the problem with an intermediate step. Select all of the one kind of updates into a table (resolving them on the way) - select all of the second types into the same table (resolving them as well) and doing a single select update (or update join). But in that case it might even be better to build specific update statements (assuming you're not using esql/c), dump them into a file and the dbaccess the file. Depends on how many you want to do. If you're dealing with a LOT of data, and you don't have XPS, and you want to apply a lot of updates - then you might consider using HPL to unload the data which does not need to be updated (you can use a pipe and pump it right back into another table through another HPL job) and then unloading the data which DOES need to be changed through the same sort of mechanism - again resolving it on the way, and pump that into the same destination table, drop the original table and rename the new table to the old name. Sounds sort of messy, but can dramatically outperform a cursor when dealing with large data sets. That said. I would stay away from exotic SQL scripts which tried to do wierd updates and go to a procedural language against an indexed table if the update was not easy to define.... (of course > I < would waste at least half a day first trying to figure out a way to easily define the update, changing the data to be updated into a consistent form before applying it...) cheers j. (who has begun to realize that he doesn't do OLTP anymore) -----Original Message----- From: Norman Erickson Lugtu [mailto:elugtu@my-deja.com] Sent: Thursday, November 09, 2000 11:51 AM To: informix-list@iiug.org Subject: Re: select in update use this: update table1 set col1 = (select table2.col1 from table2 where table1.col2 = table2.col2) where ... ; update table1 set col1 = "x" where col2 not in (select col2 from table2) ; In article <3A06EC95.DC4BFCCC@Dresdner-Bank.com>, Uwe Doetzkies <Uwe.Doetzkies@Dresdner-Bank.com> wrote: > update table1 > set col1 = > ( > select table2.col1 > from table2 > where table1.col2 = table2.col2 > ) > where ...> > sometimes it can happen, that the select doesn't return a value. in this > case table1.col1 must be set to a defined constant value, say x. > > i don't know, how to express this in informix-sql. > > i tried: > set col1 = nvl ((select...), x) > or > set col1 = (select nvl (table2.col1, x) .... > > but nothing seems right. > what can i do? > > tia > uwe > Sent via Deja.com http://www.deja.com/ Before you buy.