CODEWITHSUNDEEP
sqloracle-database
CodeKiller
CodeKiller

Reputation: 107

What's the difference between "DELETE TABLE" and "DELETE FROM" in SQL Developer?

I am using SQL Dev to manage Oracle DB and I just found out that it suggests a DELETE syntax like that :

DELETE TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280) p
   WHERE p.salary > 1700

I don't find anything on Google with "delete table" syntax, as usually the syntax is "delete from".

What's the difference with this veresion ?

DELETE FROM hr_info h
   WHERE h.department_id = 280
   AND h.salary > 1700

I mean, the first one creates a temporary table containing people from dep_id = 280 then delete all salary > 1700.

But the standard one seems faster as it directly deletes all dep_ip = 200 and salary > 1700. SO it does that in a single step while the other in two steps.

And I guess it gives the same result.

Or am I missing something ?

Thanks.

Upvotes: 2

Views: 348

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

They are doing different things, because they operate on different data types.

In the second query:

DELETE FROM hr_info h
   WHERE h.department_id = 280
   AND h.salary > 1700

the salary column is a scalar data type, presumably of number. The deletion removes the matching rows from the hr_info table, as you'd expect.

But the first query:

DELETE TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280) p
   WHERE p.salary > 1700

would error if that was the case, with 'ORA-22905: cannot access rows from a non-nested table type'.

fiddle

Which means for that query to work, the people column must be nested table of an object type, which has fields including salary. (There is also a clue in that the table subquery doesn't project a salary column.) For example, it could have been defined as something like:

create type t_person as object (person_id number, name varchar2(30), salary number)
/

create type t_people as table of t_person
/

create table hr_info (department_id number, people t_people)
nested table people store as tab_people

The TABLE() clause is then finding the hr_info row for department 280 and presenting the nested table from the people column in that row, which is projected as p. This is briefly described in the documentation. The deletion then operates on that nested table, and deletes rows from that with matching salaries; from the documentation:

For piecewise operations on nested table columns, use the TABLE expression.

The TABLE expression uses a subquery to extract the nested table, so that the INSERT, UPDATE, or DELETE statement applies to the nested table rather than the top-level table.

That deletion is then removing rows from that nested table, not from the hr_info table. There is still a single row for department 280, but its nested people table may now have fewer rows than it did before the delete.

And with this table definition the second query would now fail, with 'ORA-00904: "H"."SALARY": invalid identifier'.

fiddle

As an aside really, the FROM keyword is optional; so DELETE hr_info ... and DELETE FROM TABLE(...)... are also both valid. That documentation also refers to deleting from a nested table collection.

Upvotes: 5

Related Questions