10 Replies Latest reply: Jan 28, 2013 5:01 PM by 986981 RSS

    Update statement with inner join issues

    985586
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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.