Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] need Help with Update

Expand Messages
  • Farhan Ahmed
    Use below query like in your code also can you please post the error code so that it can be easier to understand what is the cause of that error : Declare @DB
    Message 1 of 8 , Oct 12, 2012
    • 0 Attachment
      Use below query like in your code also can you please post the error code so that it can be easier to understand what is the cause of that error :
      Declare @DB Table (ky int,I int, J Int)Declare @DB2 Table (ky int,K int, L Int)
      Insert into @DBSelect 1,10,20Union All Select 2,20,40

      Insert into @DB2Select 1,100,200Union All Select 2,200,400

      Select * from @DBSelect * from @DB2

      Update X Set X.I=Y.K+X.IFrom @Db X Inner join @Db2 Y on X.ky=Y.kyWhere X.ky=1
      Select * from @DB
      _________________________________________________________________________________________________________
      Regards,

      Farhan Ahmed
      Programmer Analyst
      The Shams Group
      Karachi, Pakistan
      (+92) 345 2523688
      View my Certifications




      To: SQLQueriesNoCode@yahoogroups.com
      From: meckeard2000@...
      Date: Thu, 11 Oct 2012 17:03:34 -0700
      Subject: Re: [SQLQueriesNoCode] need Help with Update


























      I don't think you can do that using 2 tables with a join.



      You could try a sub-query like this:



      update table

      set Blah = 0

      where

      ID in (select ID from table2 where something = 'F')



      Mark



      ________________________________

      From: faepigrl <faepigrl@...>

      To: SQLQueriesNoCode@yahoogroups.com

      Sent: Thursday, October 11, 2012 5:01 PM

      Subject: [SQLQueriesNoCode] need Help with Update







      I want to update a field in one table to another table. I think I am really close but I keep getting a syntax error.



      Here is what I am doing.....



      Update BD

      SET BD.F1 = BD.F1 + OD.F1



      From BD



      INNER JOIN OD



      ON (BD.F2 = OD.F2)



      Where BD.F3 = ' F '



      I think I am missing something and I am worried that it is not going to match the data properly. There are other fields that I am wondering if it need to look at to match up....any advice....



      Thanks new to SQL.



      [Non-text portions of this message have been removed]


















      [Non-text portions of this message have been removed]
    • Noman Aftab
      Unless there is any column which is not defined, the query appears to be perfectly fine. Share the error details.   Best Wishes, Noman Aftab  
      Message 2 of 8 , Oct 12, 2012
      • 0 Attachment
        Unless there is any column which is not defined, the query appears to be perfectly fine.
        Share the error details.
         
        Best Wishes,
        Noman Aftab

         
        http://corpus.quran.com/wordbyword.jsp



        ________________________________
        From: faepigrl <faepigrl@...>
        To: SQLQueriesNoCode@yahoogroups.com
        Sent: Friday, 12 October 2012 1:01 AM
        Subject: [SQLQueriesNoCode] need Help with Update


         
        I want to update a field in one table to another table. I think I am really close but I keep getting a syntax error.

        Here is what I am doing.....

        Update BD
        SET BD.F1 = BD.F1 + OD.F1

        From BD

        INNER JOIN OD

        ON (BD.F2 = OD.F2)

        Where BD.F3 = ' F '

        I think I am missing something and I am worried that it is not going to match the data properly. There are other fields that I am wondering if it need to look at to match up....any advice....

        Thanks new to SQL.




        [Non-text portions of this message have been removed]
      • Arnie Rowland
        I think I am missing something and I am worried that it is not going to match the data properly. ALWAYS write your query as a SELECT first, verify that it
        Message 3 of 8 , Oct 12, 2012
        • 0 Attachment
          " I think I am missing something and I am worried that it is not going to match the data properly."

          ALWAYS write your query as a SELECT first, verify that it provides the data you expect, and then change the SELECT to an UPDATE, adding the SET component.

          Regards,

          Arnie Rowland | Westwood Consulting, LLC | +1-503-246-6172

          "You cannot do a kindness too soon because you never know how soon it will be too late."
          -Ralph Waldo Emerson
        • Rob Richardson
          Unless the original poster is using PostgreSQL, the update query is not perfectly fine. According to the PostgreSQL documentation: This command conforms to
          Message 4 of 8 , Oct 12, 2012
          • 0 Attachment
            Unless the original poster is using PostgreSQL, the update query is not perfectly fine. According to the PostgreSQL documentation:

            "This command conforms to the SQL standard, except that the FROM and RETURNING clauses are PostgreSQL extensions."
            (source: http://www.postgresql.org/docs/8.4/static/sql-update.html)

            The only way I know of to do what the OP wanted is by using a sub-select, as other responders have suggested.

            RobR

            -----Original Message-----
            From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
            Sent: Friday, October 12, 2012 12:44 PM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: Re: [SQLQueriesNoCode] need Help with Update

            Unless there is any column which is not defined, the query appears to be perfectly fine.
            Share the error details.
             
            Best Wishes,
            Noman Aftab

             
            http://corpus.quran.com/wordbyword.jsp



            ________________________________
            From: faepigrl <faepigrl@...>
            To: SQLQueriesNoCode@yahoogroups.com
            Sent: Friday, 12 October 2012 1:01 AM
            Subject: [SQLQueriesNoCode] need Help with Update


             
            I want to update a field in one table to another table. I think I am really close but I keep getting a syntax error.

            Here is what I am doing.....

            Update BD
            SET BD.F1 = BD.F1 + OD.F1

            From BD

            INNER JOIN OD

            ON (BD.F2 = OD.F2)

            Where BD.F3 = ' F '

            I think I am missing something and I am worried that it is not going to match the data properly. There are other fields that I am wondering if it need to look at to match up....any advice....

            Thanks new to SQL.
          • Rob Richardson
            The original poster s update query is not perfectly fine. There are database systems (PostgreSQL is one of them, and the system I am familiar with) that allow
            Message 5 of 8 , Oct 12, 2012
            • 0 Attachment
              The original poster's update query is not perfectly fine.

              There are database systems (PostgreSQL is one of them, and the system I am familiar with) that allow a FROM clause in an UPDATE query, but that is a non-standard extension.

              If we assume standard SQL, then a sub-select will be required.

              RobR

              -----Original Message-----
              From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
              Sent: Friday, October 12, 2012 12:44 PM
              To: SQLQueriesNoCode@yahoogroups.com
              Subject: Re: [SQLQueriesNoCode] need Help with Update

              Unless there is any column which is not defined, the query appears to be perfectly fine.
              Share the error details.
               
              Best Wishes,
              Noman Aftab

               
              http://corpus.quran.com/wordbyword.jsp



              ________________________________
              From: faepigrl <faepigrl@...>
              To: SQLQueriesNoCode@yahoogroups.com
              Sent: Friday, 12 October 2012 1:01 AM
              Subject: [SQLQueriesNoCode] need Help with Update


               
              I want to update a field in one table to another table. I think I am really close but I keep getting a syntax error.

              Here is what I am doing.....

              Update BD
              SET BD.F1 = BD.F1 + OD.F1

              From BD

              INNER JOIN OD

              ON (BD.F2 = OD.F2)

              Where BD.F3 = ' F '

              I think I am missing something and I am worried that it is not going to match the data properly. There are other fields that I am wondering if it need to look at to match up....any advice....

              Thanks new to SQL.
            • T p
              dont understandif u want to update A fieldUpdate BD SET BD.F1 = BD.F1 + OD.F1 why do you write + sign? is it a varchar/int or what - otherwise u might have to
              Message 6 of 8 , Oct 14, 2012
              • 0 Attachment
                dont understandif u want to update A fieldUpdate BD
                SET BD.F1 = BD.F1 + OD.F1

                why do you write + sign?
                is it a varchar/int or what - otherwise u might have to cast it...
                it shoulc be set bd.f1=od.f1 only if u want to update the field value only with the other tablefieldvalue
                To: SQLQueriesNoCode@yahoogroups.com
                From: rdrichardson@...
                Date: Fri, 12 Oct 2012 18:03:54 +0000
                Subject: RE: [SQLQueriesNoCode] need Help with Update


























                Unless the original poster is using PostgreSQL, the update query is not perfectly fine. According to the PostgreSQL documentation:





                "This command conforms to the SQL standard, except that the FROM and RETURNING clauses are PostgreSQL extensions."


                (source: http://www.postgresql.org/docs/8.4/static/sql-update.html)





                The only way I know of to do what the OP wanted is by using a sub-select, as other responders have suggested.





                RobR





                -----Original Message-----


                From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab


                Sent: Friday, October 12, 2012 12:44 PM


                To: SQLQueriesNoCode@yahoogroups.com


                Subject: Re: [SQLQueriesNoCode] need Help with Update





                Unless there is any column which is not defined, the query appears to be perfectly fine.


                Share the error details.





                Best Wishes,


                Noman Aftab








                http://corpus.quran.com/wordbyword.jsp











                ________________________________


                From: faepigrl <faepigrl@...>


                To: SQLQueriesNoCode@yahoogroups.com


                Sent: Friday, 12 October 2012 1:01 AM


                Subject: [SQLQueriesNoCode] need Help with Update











                I want to update a field in one table to another table. I think I am really close but I keep getting a syntax error.





                Here is what I am doing.....





                Update BD


                SET BD.F1 = BD.F1 + OD.F1





                From BD





                INNER JOIN OD





                ON (BD.F2 = OD.F2)





                Where BD.F3 = ' F '





                I think I am missing something and I am worried that it is not going to match the data properly. There are other fields that I am wondering if it need to look at to match up....any advice....





                Thanks new to SQL.

















                [Non-text portions of this message have been removed]
              Your message has been successfully submitted and would be delivered to recipients shortly.