Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    38

    Unanswered: Update using a join

    I want to do something like this on an informix dynamic server 9.3 table.. the idea is using a join for update
    Code:
    	UPDATE
    	table_1
    	SET
    	table_1.column_1 = 'some value',
    	table_1.column_2 = 'some other value'
    	FROM
    	table_1, table_2
    	WHERE
    	table_1.column_2 = 'something'
    	AND
    	table_1.column_4 = table_2.column_1
    	AND 
    	table_1.column_5 = table_2.column_2
    	AND
    	table_1.column_6 BETWEEN table_2.column_4 AND table_2.column_4;
    	
    	-- Here table_1.column_6, table_2.column_4 AND table_2.column_4 are date columns
    But it does not work. It errors out saying "Syntax error". Can anyone suggest an alternative to do this? Thanks for taking a look. Best regards.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,

    Please check sintax of UPDATE, i think that clausule FROM in UPDATE not use.
    UPDATE tablename
    SET colum=value,....
    WHERE..... NOT FROM

    Gustavo.

  3. #3
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    Agree with Gustavo on the syntax comment.

    The only place you're going to use a FROM statement when doing an UPDATE is if you're doing a subquery.

    UPDATE table1
    set column 1 =
    (SELECT column1
    FROM table2
    WHERE table1.column1 = table2.column1 and
    table2.column2 = some_condition and
    blah-blah-blah ..... )

  4. #4
    Join Date
    Jan 2006
    Posts
    38
    Yeah, I agree the FROM looks wierd but in the Informix SQL guide (syntax?) they suggested this. And it is supposed to work on Parallel server. I am working with Dynamic server 9.3 and hence this was not supposed to work but I just gave a try.

    But I think I was not clear with what I wanted to do.

    I do not want to update a field using a value from second table. What I want to do is - I only need to update the rows in table 1 that have a match for the combination of the conditions as below with some known value (kind of a constant):
    Code:
    	WHERE
    	table_1.column_2 = 'something'
    	AND
    	table_1.column_4 = table_2.column_1
    	AND 
    	table_1.column_5 = table_2.column_2
    	AND
    	table_1.column_6 BETWEEN table_2.column_4 AND table_2.column_4;
    The above conditions are used to identify the rows in table 1 to be updated.

    When I try to run the update - removing the FROM - it gives me a syntax error saying table_2 is not a valid column name or something like (of course with the FROM keyword it gives the error).

    Any suggestions as to how I should go ahead with this update identifying the rows to be updated based on some join-type conditions on table_2? I thought of using a correlational query - like EXISTS but it also refuses to work since informix does not allow to give an alias to the table being updated. I think I am missing the syntax here and Informix is really so poor in documentation...

    I have gone crazy now.. and I hence will shout out loudly... PLEEAAASSEEE HELP!!!

  5. #5
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    I've dealt with this problem many times.

    Don't remember the actual errno but the message displayed is something like "You cannot modify table or view used in a subquery".

    If you're trying to update tableA with a constant value based on a set of conditions when joining tableA and tableB, then you have to
    select * from tableA into temp1 with no log
    and use temp1 in the subquery rather than tableA.

    This way Informix won't complain when you're updating tableA based on a set of conditions when joining tableB and temp1.

Posting Permissions

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