SQL issue

Dear IIUG memebers, I am attempting what I believe to be a very basic operation. I am trying to do a conditional table update from another table using a subquery. For your information I am running: IBM Informix IDS 11.10 Developer Edition Red Hat Enterprise Linux 4 Before writing the SQL statement I consulted the Informix SQL Guide that comes with the installation. The format of the Update statement is: "UPDATE update_table SET update_table_property = (SELECT source_table_property FROM source_table WHERE update_table.other_property = source_table.other_property);" I have done this successfully with one source table, however when I try another update statement of the exact same format on the same destination table but with a different source table, only changing the source_table and other_property values, the update appears to not work. I say that it appears to not work because when I am in dbaccess and run the SQL update statement it informs me that x number of rows have been updated. However when I view the contents of the table, it does not reflect any changes to the column supposedly being updated. My first inclination was that it might be an IDS Developer Edition limitation. However, the first update statement I attempted worked and updated all 2769 rows of the table. I also checked the privileges on the tables. The destination table, or table being updated, has the following privileges set: public - select: All, Update: All, Insert: Yes, Delete: Yes, Index: Yes, Alter: No. The source table has the same privileges, and the account from which I am running the SQL commands is the owner of the tables. I appreciate any help that you can provide. Peter A. Parker Clinical Research Management, Inc. Walter Reed Army Institute of Research Dept. of Chemical Information Bldg. 503, Robert Grant Ave. Silver Spring, MD 20910-7500

Peter, If dbaccess is informing you that X rows have been updated, you can feel confident that X rows were truly updated. If the field values did not change as expected, your subselect is probably not returning what you intended. One change I would make to avoid potential problems is to specify source_table.source_table_property as the subselect return value. I've seen what you described previously. In that case, the subselect inadvertantly referenced a column name which did not exist in the source_table but did exist in the update_table. HTH, Dave Griffen