access,sqlserver 常用 insert ,update ,delete 的写法
create table aa (a varchar(50),b varchar(50),c varchar(50))
create table bb (a varchar(50),b varchar(50))
insert into aa (a,b) values ('a','1')
insert into aa (a,b) values ('b','2')
insert into aa (a,b)values ('c','3')
insert into aa (a,b)values ('d','4')
insert into bb (a,b) values ('a','55')
insert into bb (a,b) values ('c','66')
select * from aa
select * from bb
-----sqlserver
insert into aa(a,b) select a,b from bb
update aa set aa.c=bb.b from bb where aa.a=bb.a
delete aa from bb where aa.a=bb.a
----access
insert into aa (a,b) select a,b from bb
当bb.c为主键或唯一索引的时候,可以这么写:
DELETE aa.* FROM aa INNER JOIN bb ON aa.c=bb.c;
其他情况下不行
DELETE * FROM aa WHERE a in (select a from bb); 无上术要求。
UPDATE aa, bb SET aa.c = bb.b WHERE aa.a=bb.a;
update aa m,aa n set m.c=n.b where m.b='22' and n.b='44'