UPDATE 语句 ORACLE /MSSQL 对比

Microsoft SQL Server                                         Oracle

Syntax:                                                                        Syntax:

UPDATE [[database.]owner.] {table                          UPDATE [user.]{table | view} [@dblink]
 | view}                                                                           SET [[ user.] {table. | view.}]
SET [[[database.]owner.] {table.                                    { column = expression | NULL | (select_
 | view.}]                                                                                 statement)
column = expression | NULL |                                         [, column = expression | NULL |
 (select_statement)                                                               (select_statement)...] |
 [, column = expression | NULL |                                          (column [, column]...) = (select_statement)}
 (select_statement)]...                                                                [WHERE {condition | CURRENT OF cursor}]
 [FROM [[database.]owner.]table |
view
 [, [[database.]owner.]table |
view]...
 [WHERE condition]

 

Microsoft SQL Server

update titles
SET pub_id = publishers.pub_id
FROM titles, publishers
WHERE titles.title LIKE 'C%'
AND publishers.pub_name = 'new age'

Oracle:

MERGE INTO titles
USING (SELECT * FROM publishers) publishers
ON ( titles.title LIKE 'C%'
  AND publishers.pub_name = 'new age' )
WHEN MATCHED THEN UPDATE SET pub_id = publishers.pub_id;

 

Microsoft SQL Server 

                    UPDATE shipping_parts
                    SET qty = 0
                    FROM shipping_parts sp, suppliers s
                    WHERE sp.supplier_num = s.supplier_num
                       AND s.location = "USA"

Oracle:

                    MERGE INTO shipping_parts sp
                    USING (SELECT * FROM suppliers) S
                    ON ( sp.supplier_num = S.supplier_num
                       AND S.location = 'USA' )
                    WHEN MATCHED THEN UPDATE SET qty = 0;