Re: Help with Informix/SQL - Novice warning!

> John Pritchard writes: > >Cathy...You are correct in that the design of the appliation is not truley >relational (bud1 is the budget for period 1, bud2 is the budget for period >2...) but we are stuck with the third party's application design. The data is >coming from an AS400 application...we just loaded it (dbload) into an informix >table so we could more easily access the information. Cathy Kipp writes: > Bummer! Oh well... back to the actual problem then... > [snip] > > This example is untested, but you could try something like this which creates > a single list built on your two criteria. The value org is only in the list > when both criteria are matched. > > update table1 > set bud1 = (select bud1 from table2 > where table1.org=table2.org and > table1.account=table2.account) > where org in (select org from table2 > where table1.org=table2.org and > table1.account=table2.account); > > I don't know if you have 4gl, but my personal preference would be to write a > simple 4GL program to handle this. Your performance would improve because > you could get rid of the subqueries, and if you are running this on a frequent > basis with logging turned on, you'll also avoid some potentially very long > transactions. I agree with Cathy that doing this in 4GL would actually be your best approach. Lacking that, I think that a simpler version of the SQL above will actually do the trick: update table1 set bud1 = (select bud1 from table2 where table1.org = table2.org and table1.account = table2.account) At first glance, this looks like it will try to update every row in table1, because there is no WHERE clause in the update. However, the WHERE in the inner SELECT should actually be enough to limit the update to the appropriate rows and to use the appropriate values. I have done this in the "O"ther DBMS; don't recall if I've tried it in Informix, but don't see why it shouldn't work here as well. As always, a backup before you start (just in case) is a good idea. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\