April 10, 2006 at 10:05 am
There are two similar tables Table1 and table2.I want to delete all rows in table1 that do not exist in table2 and have a custtype=employee'
The custid is unique when for each custype, the intention is to delete only the custtypes that are employees
This is the query I tried but it did not do the job
delete from table1 select * from table2 where CustType='Employee' and Custid not in (select custid from profile1)
Thanks for your precious time
Mike
April 10, 2006 at 10:18 am
You need an outer join on table1 and table2 using custID. Try this:
DELETE table1 FROM table1 t1
LEFT JOIN table2 t2
ON t1.custid = t2.custid
WHERE t1.custid IS NULL and t2.custtype = 'Employee'
April 10, 2006 at 12:19 pm
Thanks for your response.It did not seem to do it though .
Let me restate the problem?
Purpose .Delete all rows found in t1 that are not in t2.These rows must have a custtype of employee to qualify for the deletes.
The two tables are similar and custid and employee combination is unique
Thanks again
Mike
April 10, 2006 at 3:51 pm
I had the tables in my where clause transposed.
DELETE table1 FROM table1 t1
LEFT JOIN table2 t2
ON t1.custid = t2.custid
WHERE t2.custid IS NULL and t1.custtype = 'Employee'
If table 1 and table 2 need to be joined on more than just custid, then:
DELETE table1 FROM table1 t1
LEFT JOIN table2 t2
ON t1.custid = t2.custid and t1.employee = t2.employee
WHERE t2.custid IS NULL and t1.custtype = 'Employee'
If this does not do it for you, you need to post your table DDL for both tables as well as relationship info and sample data. I am attempting to create you a delete statement without knowing your schema. What I've posted may need tweaking based on your tables relationships. Take a look at what I've posted and look at outer joins in BOL.
April 10, 2006 at 10:00 pm
Hi,
Just Try This
delete from t1 where custid not in (select custid from t2 where custype like '%employee%')
Rgds
Jey
April 11, 2006 at 9:02 am
Thanks Johnrowan and jeyanthan for your answers.
Let me illustrate with values
t1 t2
1221 employee 1221 employee
2111 employee 2111 employee
2311 acquisition 3111 employee
3211 employee
The purpose is to compare the two tables add absent values in t1 from t2 here 3111(implemented) and remove 3211 from t1 since it is no longer in t2, leave only update entries that are employees.Leave acquisition alone
Thanks again Mike .
April 11, 2006 at 3:17 pm
INSERT INTO t1 (CustID, CustType)
SELECT t2.CustID,
t2.CustType
FROM t2
LEFT JOIN t1
ON t1.CustID = t2.CustID and t1.CustType = t2.CustType
WHERE t1.CustID IS NULL and t1.CustType IS NULL
DELETE t1
FROM t1
LEFT JOIN t2
ON t1.CustID = t2.CustID and t1.CustType = t2.CustType
WHERE t2.CustID IS NULL and t2.CustType IS NULL and t1.CustType = 'employee'
April 13, 2006 at 8:27 am
Thank you that did it .The outer join came in very handy here .....
Mike
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply