This discussion is archived
10 Replies Latest reply: Jan 28, 2013 3:01 PM by 986981 RSS

Update statement with inner join issues

985586 Newbie
Currently Being Moderated
I have searched for the answer on this and not really 100%....so figured I would ask...be nice :)

what have I done wrong? Or am I just going about this the wrong way? I have looked at the oracle docs and I can't find an example showing me this. I do see where you SET values based on the select query results. I want to update the result of a query based on the join with static values....


UPDATE table.a
SET table.a_STATUS=9,table.a.INDEX = 'N'
WHERE (SELECT table.a INNER JOIN table.b ON (table.a.COMPANY = table.b.COMPANY) AND (table.a.PO_NUMBER =table.b.PO_NUMBER) AND (table.a.PO_RELEASE =table.b.PO_RELEASE) AND (table.a.PO_CODE =table.b.PO_CODE) AND (table.a_STATUS=1) AND (table.b.CLOSED_FL = 'Y'));
  • 1. Re: Update statement with inner join issues
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!
    user11360811 wrote:
    I have searched for the answer on this and not really 100%....so figured I would ask...be nice :)

    what have I done wrong? Or am I just going about this the wrong way? I have looked at the oracle docs and I can't find an example showing me this. I do see where you SET values based on the select query results. I want to update the result of a query based on the join with static values....


    UPDATE table.a
    That's updating a table called A in a schema called TABLE (which is not a good name for any user-named object). Are those really your table and schema names? Perhaps you meant to have an underscore instead of a dot:
    UPDATE  table_a
    is much, much more reasonable. It means the table name is TABLE_A (a perfectly good name) in the current schema.
    SET table.a_STATUS=9,table.a.INDEX = 'N'
    WHERE ( ...
    There's a syntax error. You can't just say
    "WHERE (sub-query)"; it has to be
    "WHERE EXISTS (sub-suery)" or
    "WHERE (sub-query) = some_value" or
    "WHERE some_value IN (sub_query)", or something similar. WHERE can never be used without some kind of comparison operator, such as EXISTS, = or IN.
    SELECT table.a INNER JOIN table.b ON ...
    Here are some more syntax errors. The correct syntax for any query is
    SELECT  column_list
    FROM    table_name ...
    If table.a is your (first) table name, then you're missing the list of columns to SELECT, and the mandatory keyword FROM.
    (table.a.COMPANY = table.b.COMPANY) AND (table.a.PO_NUMBER =table.b.PO_NUMBER) AND (table.a.PO_RELEASE =table.b.PO_RELEASE) AND (table.a.PO_CODE =table.b.PO_CODE) AND (table.a_STATUS=1) AND (table.b.CLOSED_FL = 'Y'));
    Some general advice about UPDATE:
    If it's not obvious how to use UPDATE to do what you want, then there's a good chance that UPDATE is the wrong tool for the job. MERGE might be much simpler, and more efficient as well. This is especially likely if you need to join the table that's being updated to some other table.

     

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved. That way, the people who want to help you can re-create the problem and test their ideas.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    Simplify the problem as much as possible. Remove all tables and columns that play no role in this problem.
    If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

    Edited by: Frank Kulash on Jan 17, 2013 4:58 PM
  • 2. Re: Update statement with inner join issues
    SomeoneElse Guru
    Currently Being Moderated
    Frank is right, your code makes no sense as is (and is loaded with syntax errors).

    I'm going to take wild guess and suggest you meant to use a WHERE EXISTS clause:
    UPDATE table_a
    SET    table_a.STATUS = 9
          ,table_a.INDEX = 'N'
    WHERE  table_a.STATUS = 1      
    AND    exists
            (select 1
             from   table_b 
             where  table_a.COMPANY    = table_b.COMPANY
             AND    table_a.PO_NUMBER  = table_b.PO_NUMBER
             AND    table_a.PO_RELEASE = table_b.PO_RELEASE 
             AND    table_a.PO_CODE    = table_b.PO_CODE
             AND    table_b.CLOSED_FL  = 'Y'
            );
  • 3. Re: Update statement with inner join issues
    985586 Newbie
    Currently Being Moderated
    sorry....I did change some of what I was doing....I was just trying to make it easier...and it had the opposite effect.


    We are on Oracle 10.2

    This is the real code.....which is where I am struggling with looking at examples and have tried several combo's...this is just what I am wanting...and need the WHERE EXIST.

    UPDATE LAWSON.PORECEIVE
    SET LAWSON.PORECEIVE.R_STATUS=9,LAWSON.PORECEIVE.PORSET9_SS_SW = 'N'
    WHERE (LAWSON.PORECEIVE INNER JOIN LAWSON.PURCHORDER ON (LAWSON.PORECEIVE.COMPANY = LAWSON.PURCHORDER.COMPANY) AND (LAWSON.PORECEIVE.PO_NUMBER = LAWSON.PURCHORDER.PO_NUMBER) AND (LAWSON.PORECEIVE.PO_RELEASE =LAWSON.PURCHORDER.PO_RELEASE) AND (LAWSON.PORECEIVE.PO_CODE = LAWSON.PURCHORDER.PO_CODE) AND (LAWSON.PORECEIVE.R_STATUS=1) AND (PORECEIVE.PURCHORDER.CLOSED_FL = 'Y'));

    So if I do my select like so...which I know is a count but still....It returns X amount of rows. I only want to update for those rows.
    SELECT COUNT(*)
    FROM LAWSON.PORECEIVE INNER JOIN LAWSON.PURCHORDER ON (LAWSON.PORECEIVE.COMPANY = LAWSON.PURCHORDER.COMPANY) AND (LAWSON.PORECEIVE.PO_NUMBER = LAWSON.PURCHORDER.PO_NUMBER) AND (LAWSON.PORECEIVE.PO_RELEASE = LAWSON.PURCHORDER.PO_RELEASE) AND (LAWSON.PORECEIVE.PO_CODE = LAWSON.PURCHORDER.PO_CODE)
    WHERE LAWSON.PORECEIVE.R_STATUS=1 AND LAWSON.PURCHORDER.CLOSED_FL = 'Y';

    I don't have a create statement to show you....the data was already created.

    Edited by: user11360811 on Jan 17, 2013 2:23 PM
  • 4. Re: Update statement with inner join issues
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    user11360811 wrote:
    sorry....I did change some of what I was doing....I was just trying to make it easier...
    Simplifying is good. There are othe valid reasons (e.g. security) for posting a problem more of less different from your actual problem.
    Sonce you have to post a complete test script that other people can run, it's easy for you to run it yourself to make sure it works. If, for example, you pick a fake table name that causes errors, you'll catch that error and be able to fix it yourself.
    So, where is that test script? Where are the CREATE TABLE statements? Where are the INSERT statements? Post the information I asked for earlier, and then we can get starte.
    ... I don't have a create statement to show you....the data was already created.
    Of course it was. Write another one, or use a WITH clause, as shown in the forum FAQ {message:id=9360002}
  • 5. Re: Update statement with inner join issues
    985586 Newbie
    Currently Being Moderated
    SomeonElse.....and everyone else

    Thanks for helping....I realize I didn't have what you wanted, but I was mainly just wanting to see the idea in a syntax base which is exactly what SomeoneElse provided.

    Your code was exactly what I needed....obviously I am no SQL expert. I am normally in objective C or COBOL world, so this was just not my forte. Again...that syntax was perfect and I really do appreciate you weeding through my inexperience.

    Thanks all.
    IF anyone does need the same type of thing...this is what it amounted to.


    UPDATE LAWSON.PORECEIVE a
    SET a.R_STATUS = 9
    ,a.PORSET9_SS_SW = 'N'
    WHERE a.R_STATUS = 1
    AND exists
    (select x
    from LAWSON.PURCHORDER b
    where a.COMPANY = b.COMPANY
    AND a.PO_NUMBER = b.PO_NUMBER
    AND a.PO_RELEASE = b.PO_RELEASE
    AND a.PO_CODE = b.PO_CODE
    AND b.CLOSED_FL = 'Y'
    );
  • 6. Re: Update statement with inner join issues
    985586 Newbie
    Currently Being Moderated
    Good example/syntax of update query....just what I needed and posted the final if it would help any other newbie in my shoes.

    Thanks all
  • 7. Re: Update statement with inner join issues
    986981 Newbie
    Currently Being Moderated
    I have been searching for three days to find this exact query combination and I was so disappointed when I put in the same code (with my own table and column names of course) and got the result: Error at line 1 ORA-03113: end-of-file on communication channel. It's such a generic error that I have no idea what's wrong. We are using Oracle Database 10g Release 10.2.0.2.0
    UPDATE table_a
    SET table_a.STATUS = '1'
    WHERE table_a.STATUS is null
    AND exists
    (select 1
    from table_b
    where table_a.SPECIES = table_b.SPECIES
    AND table_a.COUNTY = table_b.COUNTY
    );
    Results in Error at line 1 ORA-03113: end-of-file on communication channel.
    UPDATE table_a
    SET table_a.STATUS = '1'
    WHERE table_a.STATUS is null
    AND exists
    (select 1
    from table_b, table_a
    where table_a.SPECIES = table_b.SPECIES
    AND table_a.COUNTY = table_b.COUNTY
    );
    Results in updating all rows in table_a where status is null; completely disregards select statement.

    Thanks, and I apologize if I should have started a new thread.
  • 8. Re: Update statement with inner join issues
    985586 Newbie
    Currently Being Moderated
    Try this

    UPDATE table_a a
    SET a.STATUS = '1'
    WHERE a.STATUS is null
    AND exists
    (select x
    from table_b b
    where a.SPECIES = b.SPECIES
    AND a.COUNTY = b.COUNTY
    );

    Same result?
  • 9. Re: Update statement with inner join issues
    986981 Newbie
    Currently Being Moderated
    Yes, the same result. I even copied and pasted your statement and the table and field names directly to make sure I wasn't mis-typing something. I forgot to add that I'm using Toad for Oracle, I'm not sure if that makes a difference.
  • 10. Re: Update statement with inner join issues
    986981 Newbie
    Currently Being Moderated
    I located the issue! Table B isn't a table, it's a view that references another view that uses a union statement that includes Table A. The circular reference (and too many layers of queries) was causing the problem. I turned the union view into a temporary table and the update query worked just fine. Thanks for the code, it saved me a lot of time when I wasn't chasing my own tail.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points