🚀 go-pugleaf

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