尝试开发的语法从基于连接第三台的另一个表更新值一个见下表

Question

测试案例显示表的结构。我需要基于tab_2和_3一个加入tab_1更新地址数据。更新脚本中所示的回报“缺少右括号”，我确信它指向的语法错误。希望得到任何帮助或指导，让语句正确地更新基表。

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语句是不完整的 - 他们甚至不指定要更新的表。他们是那么遥远，我怕我会忽略它们，因为我不明白他们怎么是salvagable * 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);