Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!watmath!clyde!cbatt!cbosgd!ucbvax!hplabs!oracle!aubery
From: aubery@oracle.UUCP (Eric aubery)
Newsgroups: net.database
Subject: Re: Problem with relational theory
Message-ID: <463@oracle.UUCP>
Date: Sun, 14-Sep-86 23:46:16 EDT
Article-I.D.: oracle.463
Posted: Sun Sep 14 23:46:16 1986
Date-Received: Sat, 27-Sep-86 09:56:56 EDT
References: <595@ur-tut.UUCP>
Reply-To: aubery@oracle.UUCP (Eric aubery)
Organization: Oracle, Belmont Ca
Lines: 46
Keywords: relational, delete

In article <595@ur-tut.UUCP> ecec@ur-tut.UUCP (Eric Carleen) writes:
>
>
>I've found that apparently I have a severe misunderstanding of
>the use of a relational data base, specifically Ingres.  I'm sending
>out a summary of my question because I've found that a number of
>other people have the same misunderstanding.
>
>Suppose that I have 3 tables of names, call them table1, table2, and 
>table3.  The names in tables 2 or 3 may or may not be in table1.  I
>would like to delete all those records in table1 that are already listed
>in the other two tables.
>
>If I give the command:
>
>	delete table1 where table1.name = table2.name
>						or table1.name = table3.name
>
>then I get what I was after: entries that match EITHER table2 OR table3
>are deleted.
>
>unless... table3 is empty.  If table3 is empty, then nothing is deleted
>from table1, regardless of whether or not there are any matching entries
>in table2.  (The work-around is obvious, but I thought unnecessary.)
>

Here is one way to specify the delete in question in Oracle, and presumably
with any SQL implementation.

delete from table1
where name in (
	select table1.name
	from table1,table2
	where
	table1.name = table2.name
	union
	select table1.name
	from table1,table3
	where
	table1.name = table3.name
)

-- 
Eric N. Aubery
ORACLE Corporation				(415)598-8022
Belmont, California				hplabs!oracle!aubery