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

need Help with Update

Expand Messages
  • faepigrl
    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
    Message 1 of 8 , Oct 11, 2012
    • 0 Attachment
      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.
    • Mark E
      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
      Message 2 of 8 , Oct 11, 2012
      • 0 Attachment
        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]
      • 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 3 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 4 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 5 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 6 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 7 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 8 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.