oracle和sqlserver一些写法区别

oracle： 

UPDATE MOPSSNRELATION A
                                   SET A.MOID = :ID
                                 WHERE EXISTS (SELECT 1
                                          FROM MOPSMANUFACTUREORDER B
                                         WHERE A.MOID = B.ID
                                           AND B.SOURCETYPE = '0020'
                                           AND B.SOURCEID = :ID)

sqlserver：

UPDATE A SET A.MOID = :ID from MOPSSNRELATION A
                                 WHERE EXISTS (SELECT 1
                                          FROM MOPSMANUFACTUREORDER B
                                         WHERE A.MOID = B.ID
                                           AND B.SOURCETYPE = '0020'
                                           AND B.SOURCEID = :ID

oracle：

DELETE FROM MOPSMANUFACTUREORDER M WHERE M.SOURCETYPE='0020' AND M.SOURCEID='{0}' AND M.MATERIALID<>'{1}'

sqlserver:

DELETE M FROM MOPSMANUFACTUREORDER M WHERE M.SOURCETYPE='0020' AND M.SOURCEID='{0}' AND M.MATERIALID<>'{1}

oracle和sqlserver如果在update和delete时使用了别名，则写法有区别，原因是oracle使用了简化的执行语句，省略掉了一些词句，而sqlserver不能省略。

oracle：

UPDATE MOPSSNRELATION A SET A.MOID='{0}' WHERE A.MOID = '{1}' AND ROWNUM<={2}

sqlserver：

UPDATE top ({2}) A SET A.MOID='{0}' from MOPSSNRELATION a WHERE A.MOID = '{1}'

sqlserver中的top相当于oracle中的rownum。

 

在oracle中可以直接使用update set（参数值，...）这样修改值，如下p1表，但sqlserver就不允许这种写法。

UPDATE {0} A SET (A.PlaceID, A.WORKCENTERID, A.WORKCENTERCODE) =
 (SELECT B.PlaceID, B.ID, B.CODE FROM MOFMWORKCENTER B WHERE A.FACTORYID = B.FACTORYID
 AND A.WORKCENTERNAME = B.NAME AND ROWNUM=1)

 那么sqlserver要实现上述功能，可以如下写法：

update person set name=m.code,qq=m.name from mobdmaterials m where m.id=1

 

update person set name=x.n,QQ=x.c from (select top(1) m.code c,m.name n from MOFMWORKCENTER m) x

同update p set p.name=x.n,p.QQ=x.c from (select top(1) m.code c,m.name n from MOFMWORKCENTER m) x,person p

update A set  A.name=BI, A.qq =BC FROM
(select top(1) B.ID BI, B.CODE BC FROM MOFMWORKCENTER B join person a on a.name = B.FACTORYID ) as MB  ,person A 

 然后P1表可以写成下面代码：

update A set A.PlaceID=BP,A.WORKCENTERID=BI,A.WORKCENTERCODE=BC FROM
(select top(1) B.PlaceID BP, B.ID BI, B.CODE BC FROM MOFMWORKCENTER B join {0} A on  A.FACTORYID = B.FACTORYID
                                       AND A.WORKCENTERNAME= B.NAME) as MB ,{0} A