question on delete statement

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Mon, 23 Jun 2003 10:49:47 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703D2B42F@lnewton.leeds.lfs.co.uk>

Hi Andrew,

You got me there !

I shall now bow out of this discussion, suitable chastised. :o(--

---

Norman Dunbar (at home on Linux)
Oracle_at_mssqlBountifulSolutions.co.uk

(Delete a Microsoft database name to reply - clue, mssql !)

---

Cheers,
Norman.

-----Original Message-----
From: andrewst [mailto:andrewst]On Behalf Of andrewst Posted At: Friday, June 20, 2003 4:50 PM Posted To: server
Conversation: question on delete statement Subject: 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

Received on Mon Jun 23 2003 - 04:49:47 CDT