SQL Server Performance

Update table from another one

Discussion in 'T-SQL Performance Tuning for Developers' started by Ilya, Aug 19, 2004.

  1. Ilya New Member

    I need to make some fields of one table eaqual to other table's ones. First I thought that I can use in UPDATE operator just name of a table but the following construction doesn't work:
    UPDATE ses_curr
    SET sub_dscp = optsesplan.sub_dscp, lecturer = optsesplan.trainer, d_kontr = optsesplan.contr_date WHERE (reg_num = optsesplan.reg_num) AND (dscp = optsesplan.dscp) AND (contr_type = optsesplan.contr_type) AND ((semester % 2) = @semester)
    I've also found about REPLACE construction but in help it's a string function and the following construction doesn't work too:
    REPLACE INTO ses_curr sub_dscp, lecturer, d_kontr SELECT sub_dscp, trainer, d_kontr FROM optsesplan WHERE (reg_num = optsesplan.reg_num) AND (dscp = optsesplan.dscp) AND (contr_type = optsesplan.contr_type) AND ((semester % 2) = @semester)
    Tell me please how to make work such updating that uses data from another table.

    cogito ergo sum
  2. FrankKalis Moderator

  3. FrankKalis Moderator

    Btw, just a tip:<br />Using the formatting capabilities when composing a message, such as [ code ] and [ / code ] makes posting easier to follow.<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  4. Ilya New Member

    quote:Originally posted by FrankKalis

    UPDATE supports a FROM clause, which you can read up in BOL.


    --Frank
    http://www.insidesql.de

    Tell me please what is BOL i.e. where I can read syntax?

    cogito ergo sum
  5. Luis Martin Moderator

    BOL: Books On Line, you can find it in SQL Server tools, same place where Enterprise Manager is.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  6. Ilya New Member

    But syntax as I could read in help
    UPDATE ses_curr SET s.sub_dscp = o.sub_dscp, s.lecturer = o.trainer, s.d_kontr = o.contr_date FROM ses_curr s, optsesplan o WHERE (s.reg_num = o.reg_num) AND (s.dscp = o.dscp) AND (s.contr_type = o.contr_type) AND ((o.semester % 2) = 1)
    doesn't work too.

    cogito ergo sum

Share This Page