DELETE 语句 oracle /mssql 对比

Microsoft SQL Server                                                           Oracle

                    Syntax:                                                                   Syntax:

                    DELETE [FROM]                                                   DELETE [FROM] [user.]{table | view}
                     [[database.]owner.]{table | view}                                  [@dblink]
                     [FROM [[database.]owner.]{table |                                    [alias]
                    view}
                     [, [[database.]owner.]{table |                                      [WHERE where_clause]
                    view}]...]
                     [WHERE where_clause]

                    Description:                                                              Description:

                    The first FROM in DELETE FROM is                            FROM is optional.
                    optional.
                                                                                                           ALIAS can be specified for the table name as a
                    The second FROM clause is a                                 correlation name, which can be used in the
                    Microsoft SQL Server or Sybase                                condition.
                    Adaptive Server extension that allows
                    the user to make deletions based on                          Deletes can only be performed through single table
                                                              views
                    the data in other tables. A subquery in
                    the WHERE clause serves the same
                    purpose.

                    Deletes can only be performed through
                    single table views.

 

 Microsoft SQL Server :

                    DELETE
                    FROM sales
                    FROM sales, titles
                    WHERE sales.title_id = titles.title_id AND titles.type = 'business'

 Oracle:

                   DELETE sales
                   WHERE ROWID IN
                    ( SELECT sales.ROWID
                      FROM sales, titles
                       WHERE sales.title_id = titles.title_id
                                AND titles.TYPE = 'business' );

 

 

 Microsoft SQL Server :

                   DELETE
                   FROM sales
                   FROM sales, table_x
                   WHERE sales.a = table_x.a
                       AND sales.b = table_x.b
                       AND table_x.c = 'd'

Oracle:

                   DELETE sales
                   WHERE ROWID IN
                    ( SELECT sales .ROWID
                      FROM sales ,
                           table_x
                       WHERE sales.a = table_x.a
                                AND sales.b = table_x.b
                                AND table_x.c = 'd' );