1 Reply Latest reply: Jul 7, 2014 12:33 PM by Frank Kulash RSS

    Need help with update command

    user5841564

      Guys,

               I am trying to update delete_dt.. I am trying it on sql formatter but giving issues with the syntax.

       

      update table stg_ccd_AMIB P set delete_dt = '2014-05-06'

      ((select acct,REC_NBR,row_NUMBER() over(partition by acct order by REC_NBR desc)as row_num

      from stg_ccd_AMIB) A WHERE

      a.ROW_NUM<=(select count(*) from stg_ccd_ibseq where acct=a.acct)) x where P.acct = X.acct

       

      Error:

      P(1,27) expected token:; ( ) , SELECT WHERE HAVING ON JOIN VALUES GROUP ORDER UNION INTERSECT

      Thanks

        • 1. Re: Need help with update command
          Frank Kulash

          Hi,

           

          If delete_dt is a DATE, then don't try to set it ot a string value, such as '2014-05-06'.  Use TO_DATE if you need to convert a string into a DATE.

          However, that probably isn't what's cause the error you reported.

           

          What are you trying to do?  It's very hared to tell just by looking at code that doesn't do it.

          The first line you posted

           

           

          user5841564 wrote:

          ...

          update table stg_ccd_AMIB P set delete_dt = '2014-05-06'

          ...

          all by itself is a valid UPDATE statement.  What does the rest of the code

          ((select acct,REC_NBR,row_NUMBER() over(partition by acct order by REC_NBR desc)as row_num

          from stg_ccd_AMIB) A WHERE

          a.ROW_NUM<=(select count(*) from stg_ccd_ibseq where acct=a.acct)) x where P.acct = X.acct

          have to do with that first line?

           

          If you want to use analytic functions, such as ROW_NUMBER, in an UPDATE statement, then you might want to use MERGE instead of UPDATE.

           

          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that 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.
          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: https://forums.oracle.com/message/9362002#9362002