update set的两种使用方法

创建数据表

--create database study;

use study;
create table S(
	SNO varchar(20) primary key not null,
	SNAME varchar(20) not null,
	SEX varchar(10),
	DEPT varchar(20),
	BIRTH datetime
);
create table C(
	CNO varchar(20) primary key not null,
	CNAME varchar(20) not null,
	PCNO varchar(20)
);
create table SC(
	SNO varchar(20) not null foreign key references S(SNO),
	CNO varchar(20) not null foreign key references C(CNO),	
	G int,
	primary key(SNO,CNO)
);

插入数据

use study;
insert into S values('001','张三','男','CS','1995-02-12');
insert into S values('002','李四','男','CS','1995-02-12');
insert into S values('003','王二','女','MA','1995-02-12');
insert into S values('004','赵六','女','MA','1995-02-12');
insert into S values('005','孙七','男','MA','1995-02-12');

insert into C values('101','计算机系统','201');
insert into C values('102','软件工程','205');
insert into C values('103','计算机网络','202');
insert into C values('104','数据结构','207');
insert into C values('105','编译原理','203');

insert into SC values('001','101',80);
insert into SC values('002','103',77);
insert into SC values('003','104',82);
insert into SC values('004','105',66);
insert into SC values('005','102',78);

更新操作

更新方式一，使用update set from where

update SC set G=G+G*0.05
from SC,S,C
where SC.SNO=S.SNO and SC.CNO=C.CNO and DEPT='MA';
select * from SC;

更新方式二，使用in子句

update SC set G=G-G*0.05 where SNO in(
	select SNO from S
	where DEPT='MA'
);
select * from SC;