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. Bummer! Oh well... back to the actual problem then... ->What we need is a process for updating a table with data from another table ->where the tables are related to each other by a combination of two fields. You originally wrote (SQL slightly reformatted): ->The script (in DBACCESS) we have been trying to use looks like... -> -> 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 -> account in (select account from table2 -> where table1.account=table2.account); -> ->The error we recieve is something about not being able to set a null to ->table1.bud1, which to me is saying the first select is not working. I ->extracted the select from this script, added table1 to the list of tables, and ->the select does work. The problem you are having is with the where clause which is related to your update. Any time org is in your first select list and account is in your second select, that is ANY combination of the results of these two lists, your update statement is goint to attempt to execute. This is obviously not what you intended. 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. Hope this helps! Regards, - Cathy -------------------------------------------------------------------------------- Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (303) 491-1294 Colorado State University Veterinary Teaching Hospital Fax: (303) 491-1205