By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,971 Members | 1,732 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,971 IT Pros & Developers. It's quick & easy.

delete child table rows based on predicates in a parent table

P: n/a
jim
I have two tables that are related by keys. For instance,

Table employee {
last_name char(40) not null,
first_name char(40) not null,
department_name char(40) not null,
age int not null,
...
}
Employee table has a primary key (combination of last_name and first_name).

Table address {
last_name char(40) not null,
first_name char(40) not null,
street char(200) not null,
city char(100) not null,
...
}
Address table has a primary key (combination of last_name, first_name and
street in which (last_name, first_name) reference (last_name, first_name) in
employee table.

Now I want to delete some rows in Address table based on department_name in
Employee table. What is sql for this delete?

I appreciate your help. Please ignore table design and I just use it for my
problem illustration.
Jim
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Something like this:

delete from address where last_name in (select last_name from employee
where department_name='100') and first_name in
(select first_name from employee where department_name='100')

but check the logic to ensure you don't delete something incorrectly.

jim wrote:
I have two tables that are related by keys. For instance,

Table employee {
last_name char(40) not null,
first_name char(40) not null,
department_name char(40) not null,
age int not null,
...
}
Employee table has a primary key (combination of last_name and first_name).

Table address {
last_name char(40) not null,
first_name char(40) not null,
street char(200) not null,
city char(100) not null,
...
}
Address table has a primary key (combination of last_name, first_name and
street in which (last_name, first_name) reference (last_name, first_name) in
employee table.

Now I want to delete some rows in Address table based on department_name in
Employee table. What is sql for this delete?

I appreciate your help. Please ignore table design and I just use it for my
problem illustration.
Jim


Nov 12 '05 #2

P: n/a
AK
the correct query is:

delete from address where
EXISTS(SELECT * FROM EMPLOYEE WHERE EMPLOYEE.LAST_NAME=address.last_name
AND EMPLOYEE.FIRST_NAME=ADDRESS.FIRST_NAME
AND EMPLOYEE.DEPARTMENT='100')

let us suppose that it deletes 2 guys:
SMITH, JOHN
LEE, JOSEPH

This incorrect query:

delete from address where last_name in (select last_name from employee
where department_name='100') and first_name in
(select first_name from employee where department_name='100')


would also delete

LEE, JOHN
SMITH, JOSEPH

even if their dempartments are '200' and '300'
Nov 12 '05 #3

P: n/a
Could be simplified as:

delete from address where (last_name, first_name) in
(select last_name, first_name
from employee where depart_name = '100')

This avoids the second sub-select.

--Mike
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<bt**********@hanover.torolab.ibm.com>...
Something like this:

delete from address where last_name in (select last_name from employee
where department_name='100') and first_name in
(select first_name from employee where department_name='100')

but check the logic to ensure you don't delete something incorrectly.

jim wrote:
I have two tables that are related by keys. For instance,

Table employee {
last_name char(40) not null,
first_name char(40) not null,
department_name char(40) not null,
age int not null,
...
}
Employee table has a primary key (combination of last_name and first_name).

Table address {
last_name char(40) not null,
first_name char(40) not null,
street char(200) not null,
city char(100) not null,
...
}
Address table has a primary key (combination of last_name, first_name and
street in which (last_name, first_name) reference (last_name, first_name) in
employee table.

Now I want to delete some rows in Address table based on department_name in
Employee table. What is sql for this delete?

I appreciate your help. Please ignore table design and I just use it for my
problem illustration.
Jim

Nov 12 '05 #4

P: n/a
Wouldn't recent SQL enhancements make this a little easier?:

delete from address
where (last_name, first_name) in
(select last_name, first_name from employee where department_name='100')
Nov 12 '05 #5

P: n/a
That design is soooo bad that I have to beat you for it. You also
should store the birthdate of a person and compute their age as
needed. Use plural table names, since they are sets and not scalars.
And the size of your columns is absurd -- ever hear of any place on
Earth that has a CHAR(200) name?? Well, someone will load that kind
of garbage into your database since you failed to design the tables
correctly.

But now to the question with cleaner data.

CREATE TABLE Personnel
(last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
department_name CHAR(5) NOT NULL,
birthdate DATE NOT NULL,
...
PRIMARY KEY (last_name, first_name));

CREATE TABLE Addresses
(last_name CHAR(20) NOT NULL,
first_name CHAR(20) NOT NULL,
street CHAR(35) NOT NULL,
city CHAR(20) NOT NULL,
FOREIGN KEY (last_name, first_name)
REFERENCES Personnel (last_name, first_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
...
PRIMARY KEY (last_name, first_name, street));

When you delete a row in Personnel, the corresponding rows in
Addresses will also be deleted by the system.
Nov 12 '05 #6

P: n/a
jo*******@northface.edu (--CELKO--) wrote in message news:<a2**************************@posting.google. com>...
And the size of your columns is absurd -- ever hear of any place on
Earth that has a CHAR(200) name??
Ah, but there's tourist value in long names, so just they keep getting
longer (so nice resizeable VARCHAR would be better). See
http://en2.wikipedia.org/wiki/Longest_word_in_English ...
There is some debate as to whether or not a place name
is a legitimate word. Without entering that debate, let
it be noted that the longest officially recognized place
name in an English-speaking country is
Taumatawhakatang*ihangakoauauot*amateaturipukaka*p ikimaunga*horonuku*pokaiwhenuak*itanatahu
(85 letters) which is a hill in New Zealand.

The 58 letter name
Llanfair*pwllgwyngyll*gogerychwyrndrobwll*llantysi liogogogoch
is the famous name of a town in Wales in the United Kingdom.
Critics, however, have alleged that the name (which was adopted
in the mid 19th century) was contrived solely to be "the
longest name of a town in Great Britain". The longest station
name in the UK, at 68 letters, is:
Gorsafawddacha'idraigodanheddogleddollônpenrhynare urdraethceredigion
which was contrived to beat the Welsh Town.


HTH :-)
DG
Nov 12 '05 #7

P: n/a
In article <7f**************************@posting.google.com >,
Database Guy (db******@hotmail.com) says...
jo*******@northface.edu (--CELKO--) wrote in message news:<a2**************************@posting.google. com>...
And the size of your columns is absurd -- ever hear of any place on
Earth that has a CHAR(200) name??


Ah, but there's tourist value in long names, so just they keep getting
longer (so nice resizeable VARCHAR would be better). See
http://en2.wikipedia.org/wiki/Longest_word_in_English ...


And they missed the longest town name in the world :)

http://www.fun-with-words.com/longest_place_names.html

De longest townname from some place in Thailand (163 characters):

Krung*thep*maha*nakorn*amorn*ratana*kosin*mahintar *ayutthay*amaha*dilok*phop*
noppa*ratrajathani*burirom*udom*rajaniwes*mahasat* harn*amorn*phimarn*avatarn*
sathit*sakkattiya*visanukamprasit

It seems to be official name of Bangkok.
Nov 12 '05 #8

P: n/a
Hey Joe,

Don't beat up on Jim because you didn't read his message. Clearly
says, "Please ignore table design and I just use it for my
problem illustration."

Now, I'm gonna beat up on you! Did you really knowingly violate one
of the prime laws of database normalization? Did you really write:

CREATE TABLE Personnel
(last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
department_name CHAR(5) NOT NULL,
birthdate DATE NOT NULL,
...
PRIMARY KEY (last_name, first_name));

CREATE TABLE Addresses
(last_name CHAR(20) NOT NULL,
first_name CHAR(20) NOT NULL,
street CHAR(35) NOT NULL,
city CHAR(20) NOT NULL,
FOREIGN KEY (last_name, first_name)
REFERENCES Personnel (last_name, first_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
...
PRIMARY KEY (last_name, first_name, street));

.... thus putting last_name and first_name in two different tables?
And using them in primary keys? Should there not be a unique_id,
created any way you like, eg with GENERATE_UNIQUE(), so that these and
other tables can be connected without duplicating data across tables?
Would also eliminate need for ON UPDATE CASCADE. Also the fatal error
when SMITH, JOHN (SR) and SMITH, JOHN (JR) live at the same address
and didn't have JR and SR entered. (PRIMARY KEY must be unique in
this setting.)

In any case, PRIMARY KEY (last_name, first_name) is incorrect since,
in a large table, there will be many SMITH, JOHN 's.

And... If names are in VARCHAR() in table Personnel, why are they in
CHAR() in table Addresses? And, if you object to Jim's random choice
of lengths to illustrate sample fields, how can you recommend
VARCHAR(20) for last_name? Many women hyphenate their names,
resulting in unbelievably long concatenations.

Oy... "That design is soooo bad that I have to beat you for it."

SS

PS Do you really think that people will use plural table names for a
mathematical nicety? Do they say that data "are," or data "is?"
jo*******@northface.edu (--CELKO--) wrote in message news:<a2**************************@posting.google. com>...
That design is soooo bad that I have to beat you for it. You also
should store the birthdate of a person and compute their age as
needed. Use plural table names, since they are sets and not scalars.
And the size of your columns is absurd -- ever hear of any place on
Earth that has a CHAR(200) name?? Well, someone will load that kind
of garbage into your database since you failed to design the tables
correctly.

But now to the question with cleaner data.

CREATE TABLE Personnel
(last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
department_name CHAR(5) NOT NULL,
birthdate DATE NOT NULL,
...
PRIMARY KEY (last_name, first_name));

CREATE TABLE Addresses
(last_name CHAR(20) NOT NULL,
first_name CHAR(20) NOT NULL,
street CHAR(35) NOT NULL,
city CHAR(20) NOT NULL,
FOREIGN KEY (last_name, first_name)
REFERENCES Personnel (last_name, first_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
...
PRIMARY KEY (last_name, first_name, street));

When you delete a row in Personnel, the corresponding rows in
Addresses will also be deleted by the system.

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.