尝试开发语法，以便根据第三个表的联接使用另一个表的值更新一个表。

Question

下面的测试用例显示了表的构造。我需要基于tab_1和_3上的连接更新tab_2中的地址数据。显示的更新脚本返回‘缺失的右括号’，我确信它指向语法中的错误。希望在使语句正确更新基表方面提供任何帮助或指导。

create table tab_1(address varchar2(25), city varchar2(25), state varchar2(2),zip varchar2(10), office_id varchar2(25));

create table tab_2 (company varchar2(25), office varchar2(25), address_id varchar2(5), office_id varchar2(5));

create table tab_3 (address_id varchar2(5), address varchar2(25), city varchar2(25), state varchar2(2),zip varchar2(10));


insert into tab_1(office_id) values(46);

insert into tab_2(company, office, address_id, office_id) 
    values('Stone', 'north', '45', '15');
insert into tab_3(address_id, address, city, state, zip) 
    values('15', '12Main', 'York', 'NY', '12345');


ALTER TABLE TAB_1 ADD 
CONSTRAINT tab_1_PK
 PRIMARY KEY (OFFICE_ID)
 ENABLE
 VALIDATE;

 ALTER TABLE TAB_2 ADD 
CONSTRAINT tab_2_PK
 PRIMARY KEY (OFFICE_ID)
 ENABLE
 VALIDATE;

ALTER TABLE TAB_3 ADD 
CONSTRAINT tab_3_PK
 PRIMARY KEY (ADDRESS_ID)
 ENABLE
 VALIDATE;




update (select tab_3.address, tab_3.city, tab_3.state, tab_3.zip, tab_1.address,     tab_1.city, tab_1.state, tab_1.zip
        FROM
    INNER JOIN tab_1 ON (tab_1.office_id=tab_2.office.id) 
    INNER JOIN tab_3 ON (tab_2.address_id = tab_3.address_id))
        SET tab_1.address=tab_3.address, tab_1.city=tab_3.city, tab_1.state=tab_3.state, tab_1.zip=tab_3.zip;


UPDATE ( SELECT src.x src_x, src.y src_y , tgt.x tgt_x, tgt.y tgt_y FROM src 
INNER JOIN tgt ON ( src.id = tgt.id ) ) SET tgt_x = src_x , tgt_y = src_y

*******************************************************

UPDATE tab_1
   SET (address,
        city,
        state,
        zip) =
          (SELECT (address, city, state, zip)
             FROM tab_3, tab2
            WHERE     tab_1.office_id = tab_2.office_id
                  AND tab_2.address_id = tab_3.address_id);

Answer 1

前两个update语句是不完整的-它们甚至没有指定要更新哪个表。他们离我这么远，恐怕我会忽略他们，因为我看不出他们有多好*8-)

第三个作为额外的括号集；您不需要它们(而且它们是无效的)在子查询中选择的列列表周围，它试图将其解释为不存在的进一步子查询。您还在其中一个表名中输入了一个错误：

UPDATE tab_1
   SET (address, city, state, zip) =
       (SELECT address, city, state, zip
          FROM tab_3, tab_2
         WHERE tab_1.office_id = tab_2.office_id
           AND tab_2.address_id = tab_3.address_id);

我建议您使用现代join语法，特别是如果这是相当新的，而且您还没有学习(可以说是)坏习惯：

UPDATE tab_1
   SET (address, city, state, zip) =
       (SELECT address, city, state, zip
          FROM tab_2
          JOIN tab_3
            ON tab_3.address_id = tab_2.address_id
         WHERE tab_2.office_id = tab_1.office_id);