SQL Server Performance

Update SQL

Discussion in 'SQL Server 2005 General Developer Questions' started by ralph1972, Jan 2, 2007.

  1. ralph1972 New Member

    Hello,

    I've a question regarding an UPDATE command

    I have a table A with an average net price and an average brut price

    I want to update these fields where they are both 0 and take the net and brut price from another table B where the foreign keys are the same.

    Fe


    Table A
    ID Col WS ArtID ArtCol ArtWS Net Brut
    1 1 1 1 1 1 0 0
    2 1 1 2 1 1 0 0
    3 1 1 5 1 1 0 0

    Table B
    ID Col WS ArtID ArtCol ArtWS Net Brut
    1 1 1 1 1 1 4 8
    2 1 1 2 1 1 5 7
    3 1 1 5 1 1 7 7

    How can I update the values NET , BRut from table B into table A?

    Thanks

    Ralph
  2. ranjitjain New Member

    UPDATE tableA
    SET net=b.net,
    brut=b.brut
    FROM tableA a JOIN tableB b ON
    a.id=b.id
    WHERE a.net=0 and a.brut=0
  3. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ralph1972</i><br /><br />Hello,<br /><br />I've a question regarding an UPDATE command<br /><br />I have a table A with an average net price and an average brut price<br /><br />I want to update these fields where they are both 0 and take the net and brut price from another table B where the foreign keys are the same.<br /><br />Fe <br /><br /><br />Table A <br />ID Col WS ArtID ArtCol ArtWS Net Brut<br />1 1 1 1 1 1 0 0<br />2 1 1 2 1 1 0 0<br />3 1 1 5 1 1 0 0<br /><br />Table B<br />ID Col WS ArtID ArtCol ArtWS Net Brut<br />1 1 1 1 1 1 4 8<br />2 1 1 2 1 1 5 7<br />3 1 1 5 1 1 7 7<br /><br />How can I update the values NET , BRut from table B into table A?<br /><br />Thanks<br /><br />Ralph<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />Try this:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Use DatabaseName<br />GO<br /><br />UPDATE A<br />SET NET = b.NET<br />,Brut = b.Brut<br /><br />FROMTableA A<br />JOINTableB B<br />ONA.ID = B.ID<br />WHEREa.NET = 0<br />ANDa.Brut = 0<br /><br />GO<br /></font id="code"></pre id="code"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br /><br />Oops, someone has already posted. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Thanks,<br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
  4. ralph1972 New Member

    Thanks for all the help

    This topic can be closed.

Share This Page