Re: question on delete statement

Originally posted by Norman Dunbar
> Hi Fabio,
>
> DELETE FROM table_name
> WHERE some_condition;
>
> I don't expect your example statement to actually be accepted
> in SQLPlus
> or anything else for that matter. To me it has a sytax error as you
> did
> not specify the tablename to delete from. I haven't tested it - as I
> suspect is the case with you.
>
>

Norman, you really should have tested it (as Fabio probably did!):

SQL> delete (select * from emp, dept where emp.deptno = dept.deptno and SQL> dept.loc='NEW YORK');

3 rows deleted.

It only works if exactly ONE of the tables in the subquery is "key preserved", i.e. each primary key value in the table is guaranteed to appear ONCE only. In my example, EMP is key preservered but DEPT is not (same DEPT record can be joined to many EMP records), so it is the EMP record that gets deleted.

--
Posted via http://dbforums.com