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

Re: [SQLQueriesNoCode] need Help with Update

Expand Messages
  • 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 1 of 8 , Oct 11, 2012
      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 2 of 8 , Oct 12, 2012
        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 3 of 8 , Oct 12, 2012
          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 4 of 8 , Oct 12, 2012
            " 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 5 of 8 , Oct 12, 2012
              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 6 of 8 , Oct 12, 2012
                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 7 of 8 , Oct 14, 2012
                  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.