Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    12

    Unanswered: 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.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    The Update-statement is the way to go, you'll need to add both tables in the from-clause, fe:

    set nocount on

    create table table1 (myint1 integer, myvalue1 varchar(10))
    create table table2 (myint2 integer, myvalue2 varchar(10))

    insert into table1 (myint1, myvalue1) values (1, 'aa')
    insert into table1 (myint1, myvalue1) values (2, 'cc')
    insert into table2 (myint2, myvalue2) values (1, 'bb')

    select * from table1

    update table1
    set myvalue1 = t2.myvalue2
    from table1 t1
    , table2 t2
    where t1.myint1 = t2.myint2

    select * from table1

    drop table table1
    drop table table2

  3. #3
    Join Date
    Jul 2004
    Posts
    12
    Thank you, contruction
    UPDATE ses_curr SET sub_dscp = t2.sub_dscp FROM ses_curr t1, optsesplan t2 WHERE (t1.reg_num = t2.reg_num) AND (t1.dscp = t2.dscp) AND (t1.checktype = t2.contr_type) AND ((t2.semester % 2) = @semester)
    works.

Posting Permissions

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