update from select 

 1 CREATE TABLE dualx(  
 2        x_id VARCHAR(5) NOT NULL ,  
 3        x_con VARCHAR(10)  
 4 )  
 5   
 6 CREATE TABLE dualy(  
 7        y_id VARCHAR(5) NOT NULL ,  
 8        y_con VARCHAR(10)  
 9 )  
10   
11 INSERT INTO dualx VALUES ('1','xxx');  
12 INSERT INTO dualx VALUES ('2','xxx');  
13 INSERT INTO dualx VALUES ('3','xxx');  
14   
15 INSERT INTO dualy VALUES ('1','111');  
16 INSERT INTO dualy VALUES ('2','222');  
17 INSERT INTO dualy VALUES ('3','333');  
18   
19 UPDATE dualx   SET (x_con) = (SELECT y.y_con  FROM  dualy y WHERE dualx.x_id = y.y_id)  
20   
21 SELECT * FROM dualx;  
22 SELECT * FROM dualy;  
23 DROP TABLE dualx;  
24 DROP TABLE dualy;
View Code
 

UPDATE dualx SET (x_con) = (SELECT y.y_con FROM dualy y WHERE dualx.x_id = y.y_id) 其中SELECT y.y_con FROM dualy y WHERE dualx.x_id = y.y_id不能返回多行，否则报错。

posted @ 2016-01-15 09:47  fashflying  阅读(858)  评论(0)  编辑  收藏  举报