Deleting the contents of a child table from a parent
-
HI,
I have a master table with 100k names and addresses. I have another table with 19,000 names and addresses. I want to remove the 19 from the 100k and see what I have left.
I have been using "select * from 100k where exists(select * from 19k)"
but I am getting syntax errors - I have tried BOL but not seeing what I need
Any ideas?
thanks,
Paul
-
delete parent
from parent p
join child c
on c.all_fields = p.all_fields
--tested below
create table parent
(
p_id int identity(1,1) primary key,
vala int,
valb int
)
go
create table child
(
p_id int identity(1,1) primary key,
vala int,
valb int
)
declare @int int
set @int = 0
while @int < 100
begin
insert into parent(vala,valb)
select @int, @int-1
if @int%3 = 0
begin
insert into child(vala,valb)
select @int, @int-1
end
set @int = @int + 1
end
go
delete parent
from parent p
join child c
on
c.vala = p.vala
and c.valb = p.valb
HTH
Mathew J Kulangara
sqladventures.blogspot.com -
You have the right idea. The EXISTS is essentially done for each record in the parent and SELECT * FROM 19K will always return all the records in 19K so EXISTS will always be true. There are no fields specified to correlate the two queries.
A join, as Mathew has pointed out, is a better way to go. I think that you just want to see the data and not delete it right away. I would view it first before deleting it anyway
.Use a LEFT OUTER JOIN to get all 100K records back, associated with the 19K if they exist, then use the WHERE clause to filter out those where the 19K portion is null (added by the JOIN).
SELECT * FROM 100K LEFT OUTER JOIN 19K ON 100K. = 19K. ...
WHERE 19K. IS NULL
represents the column(s) that must match for the two to be the same.
WHERE ... IS NULL filters the output of the join where the records have been added by the OUTER option.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply