SQL Update statement with subquery join

pparker · #1 (**permalink**) 06-05-08, 14:39

Greetings,
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:

Code:

"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:

Code:

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:

Code:

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.

Thanks,
Peter

ceinma · #2 (**permalink**) 06-06-08, 09:02

Probably you are comparing two differs data type columns OR your data are differ.

Try this way.

Code:

SELECT count(*)  FROM  update_table 
WHERE exists (SELECT source_table_property FROM source_table WHERE update_table.compare_property = source_table.compare_property);"

To understand what is wrong you can try something like this:

Code:

SELECT a.compare_property, b.update_table_property 
FROM update_table a, outer source_table b
WHERE a.compare_property = b.compare_property;

pparker · #3 (**permalink**) 06-13-08, 13:33

Thank you for your help!

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.

Thanks again.

Internet Services

Web Development

Digital Marketing

Consumer Tech