Unanswered: SQL Update statement with subquery join
I am attempting what I believe to be a very basic operation. I am trying to do a table update using a subquery that contains a join operation.
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.compare_property = source_table.compare_property);"
I have done this successfully with another source table; however when I try this update statement of the exact same format on the same destination table but a different source table, only changing the source_table and compare_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 updates.
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 has ownership of the tables.
I verified that the data types of both the value being updated (update_table_property) and the value in the join (compare_property) are of the same type, CHAR 6 and DECIMAL (9,1) respectively.
I suspect that my subquery is not returning what I would expect it to, but I’m not sure how to test it. I tried to test it with the following query that uses Aliases:
SELECT a.compare_property, b.update_table_property FROM update_table a, source_table b WHERE (a.compare_property = b.compare_property);
This query returns no results, but since the join is not a subquery to an update statement, I’m not sure that it should.
I really appreciate any help that you can provide.
It turns out that the compare_property data type used in the join was the problem. I was using a data type of CHAR(6) and was using it to store numbers in the range from 1 to 900. Aparently equality comparisons on char fields storing numbers do not work well in Informix.
I changed the CHAR(6) to FLOAT and the update worked perfectly.