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.


    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

    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