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'