SQL Server Performance Forum – Threads Archive

Update table from another one

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

---

UPDATE supports a FROM clause, which you can read up in BOL.
–Frank
http://www.insidesql.de

---

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 />

---

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

---

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.

---

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

---

]]>