Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Unanswered: SQL Update statement with subquery join

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Jun 2008
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •