Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2012
    Posts
    4

    Unanswered: Join two tables and update values in one

    I have two tables A and b

    A
    projectID Setid value
    301 1 abc
    301 2 xyz
    302 4 def
    ..... .... ..
    B

    SettingsId Setvalue

    1 ter
    2 yet
    4 44
    ... ....

    i want to update all the Setid of table A with that of values from table b .How can i do this

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by itsarunsunny View Post
    i want to update all the Setid of table A
    i'm gonna guess that you really didn't mean what you said, because it doesn't make any sense

    i think what you want is to update all value columns, based on matching the settings ids
    Code:
    UPDATE tableA
       SET value = t2.Setvalue 
      FROM tableA t1
    INNER
      JOIN tableB t2
        ON t1.Setid = t2.SettingsId
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    4
    Sorry for causing confusion but u guessed it right.and i want to update all values in a stored procedure .I have used a while loop to do it and got it .i am not sure weather it is the right way

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, a loop in a stored procedure is ~not~ the right way

    the query i gave you updates everything all at once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2012
    Posts
    4
    thanks for help dude

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937 View Post
    no, a loop in a stored procedure is ~not~ the right way
    even if you are using set based batches because you are updating one billion records?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus View Post
    even if you are using set based batches because you are updating one billion records?
    oh sure, set up a straw man outlier scenario, and you can justfy ~anything~

    where was the hint of those conditions in the original post, sean?

    a billion project settings? please to getting real, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am sorry. I always question blanket statements and universal rules that involve the words ALWAYS or NEVER and the like. The real answer, and especially in sql, is that it depends.

    As for a billion project settings, I know personally that America's biggest oil company has a EAV system designed to do just that.

    Outlier? I moved a half billion records yesterday in DEV in 4999 record batches.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •