-- 建表语句 CREATE TABLE emp ( ename varchar(20) null, hirdate date null, sal numeric(8,2) null, deptno TINYINT null );CREATE TABLE dept ( deptno TINYINT not null, deptname varchar(20) null );INSERT INTO emp(ename,hirdate,sal,deptno) SELECT 'zzx','2000-01-01','100',1 UNION ALL SELECT 'lisa','2003-02-01','200',2 UNION ALL SELECT 'bjguan','2004-04-02','100',1 UNION ALL SELECT 'dony','2005-02-05','2000',4 ;INSERT INTO dept(deptno,deptname) SELECT 1,'tech' UNION ALL SELECT 2,'sale' UNION ALL SELECT 5,'fin';-- 更新语句: UPDATE emp a,dept b SET a.sal=a.sal*b.deptno,b.deptname=a.ename WHERE a.deptno=b.deptno;最后结果集: emp表: ename hirdate sal deptno zzx 2000-01-01 100 1 lisa 2003-02-01 400 2 bjguan 2004-04-02 100 1 dony 2005-02-05 2000 4dept表: deptno deptname 1 zzx 2 lisa 5 fin
更新了dept表的deptname字段,更新值为对应emp表中的ename的值
对应法则为emp表的dept值和dept表的dept值相等
-- 建表语句
CREATE TABLE emp
(
ename varchar(20) null,
hirdate date null,
sal numeric(8,2) null,
deptno TINYINT null
);CREATE TABLE dept
(
deptno TINYINT not null,
deptname varchar(20) null
);INSERT INTO emp(ename,hirdate,sal,deptno)
SELECT 'zzx','2000-01-01','100',1 UNION ALL
SELECT 'lisa','2003-02-01','200',2 UNION ALL
SELECT 'bjguan','2004-04-02','100',1 UNION ALL
SELECT 'dony','2005-02-05','2000',4 ;INSERT INTO dept(deptno,deptname)
SELECT 1,'tech' UNION ALL
SELECT 2,'sale' UNION ALL
SELECT 5,'fin';-- 更新语句:
UPDATE emp a,dept b
SET a.sal=a.sal*b.deptno,b.deptname=a.ename
WHERE a.deptno=b.deptno;最后结果集:
emp表:
ename hirdate sal deptno
zzx 2000-01-01 100 1
lisa 2003-02-01 400 2
bjguan 2004-04-02 100 1
dony 2005-02-05 2000 4dept表:
deptno deptname
1 zzx
2 lisa
5 fin
deptno为1、2 的数据会被修改。