The statement illustrated in the following table removes rows from tables and rows from tables referenced in views.
Remove Second FROM Clause:
Remove the second FROM clause from the DELETE statements.
Convert the following Microsoft SQL Server and Sybase Adaptive Server query: DELETE
FROM sales
FROM sales, titles
WHERE sales.title_id = titles.title_id AND titles.type = 'business'
Table 2–10 DELETE Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server
Microsoft SQL Server and Sybase
Adaptive Server Oracle
Syntax: DELETE [FROM] [[database.]owner.]{table | view} [FROM [[database.]owner.]{table | view} [, [[database.]owner.]{table | view}]...] [WHERE where_clause] Syntax:
DELETE [FROM] [user.]{table | view} [@dblink]
[alias]
[WHERE where_clause]
Description:
The first FROM in DELETE FROM is optional.
The second FROM clause is an Microsoft SQL Server and Sybase Adaptive Server extension that allows the user to make deletions based on the data in other tables. A subquery in the WHERE clause serves the same purpose.
Deletes can only be performed through single table views.
Description: FROM is optional.
ALIAS can be specified for the table name as a correlation name, which can be used in the condition.
Deletes can only be performed through single table views
to the following in Oracle: DELETE FROM sales WHERE title_id in ( SELECT title_id FROM titles
WHERE type = 'business' )
Remove the second FROM even if the WHERE contains a multi-column JOIN. Convert the following Microsoft SQL Server and Sybase Adaptive Server query: DELETE
FROM sales
FROM sales, table_x WHERE sales.a = table_x.a AND sales.b = table_x.b AND table_x.c = 'd'
to the following in Oracle: DELETE FROM sales WHERE ( a, b ) in ( SELECT a, b FROM table_x WHERE c = 'd' )
Operators
Comparison Operators
The following table compares the operators used in the Microsoft SQL Server and Sybase Adaptive Server, and Oracle databases. Comparison operators are used in WHERE clauses and COLUMN checkconstraints/rules to compare values
Table 2–11 Comparison Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server Operator Same in All Three Databases Microsoft SQL Server and Sybase Adaptive Server
Only Oracle Only
Equal to = Not equal to != <> ^= Less than < Greater than >
Less than or equal to <= !> Greater than or equal to >= !< Greater than or equal to x and
less than or equal to y
BETWEEN x AND y Less than x or greater than y NOT BETWEEN
x AND y Pattern Matches
a followed by 0 or more characters
a followed by exactly 1 character a followed by any character between x and z
a followed by any character except those between x and z a followed by % LIKE 'a%' LIKE 'a_' LIKE'a[x-z]' LIKE'a[^x-z]' LIKE 'a\%' ESCAPE '\'
Does not match pattern NOT LIKE No value exists IS NULL A value exists IS NOT NULL At least one row returned by
query
EXISTS (query) No rows returned by query NOT EXISTS
(query) Equal to a member of set IN
=ANY
Recommendations:
1. Convert all !< and !> to >= and <=
Convert the following in Microsoft SQL Server and Sybase Adaptive Server: WHERE col1 !< 100
to this for Oracle: WHERE col1 >= 100
Not equal to a member of set NOT IN != ANY <> ANY
!= SOME <> SOME
Less than a member of set < ANY < SOME Greater than a member of set > ANY > SOME Less than or equal to a member of
set
<= ANY !> ANY <= SOME
Greater than or equal to a member of set
>= ANY !< ANY >= SOME
Equal to every member of set =ALL
Not equal to every member of set != ALL <> ALL Less than every member of set < ALL Greater than every member of set > ALL Less than or equal to every
member of set
<= ALL !> ALL
Greater than or equal to every member of set
>= ALL !< ALL
Table 2–11 Comparison Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server (Cont.)
Operator
Same in All Three Databases
Microsoft SQL Server and Sybase Adaptive Server
Method 1 - Eliminating use of [ ]:
Use this method with the SUBSTR () function if possible. SELECT title
from titles
where substr (titles,1,1) in
('A', 'B', 'C', 'D', 'E', 'F')
Method 2 - Eliminating use of [ ]:
The second method uses the % construct. SELECT title
FROM titles
WHERE (title like 'A%' OR title like 'B%' OR title like 'C%' OR title like 'D%' OR title like 'E%' OR title like 'F%') 3. Change NULL constructs:
The following table shows that in Oracle, NULL is never equal to NULL. Change the all = NULL constructs to IS NULL to retain the same functionality.
If you have the following in Microsoft SQL Server and Sybase Adaptive Server: WHERE col1 = NULL
Convert it as follows for Oracle:
Table 2–12 Changing NULL Constructs
NULL Construct
Microsoft SQL Server and
Sybase Adaptive Server Oracle where col1 = NULL depends on the data FALSE where col1 != NULL depends on the data TRUE
where col1 IS NULL depends on the data depends on the data where col1 IS NOT
NULL
depends on the data depends on the data
WHERE col1 IS NULL