Delete rows in one table by referencing another table info

Dev Shed Forums Sponsor:

Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now!

September 16th, 2004, 04:54 PM

confuzed2xover

Contributing User

Join Date: Apr 2004

Posts: 185

Time spent in forums: 1 Day 5 h 45 m 53 sec
Reputation Power: 5

Delete rows in one table by referencing another table info

I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.

Here is a basic breakdown of what I am trying to do:

Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)

Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)

I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.

Code:

SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x

This did not seem to do what I needed, it did not delete any rows at all.

I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2

Then I tried another scenerio that I also needed to do:

Code:

SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'

'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.

Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.

Any ideas on what I am doing wrong here?

September 16th, 2004, 05:07 PM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,766

Time spent in forums: 2 Weeks 4 Days 6 h 24 m 8 sec
Reputation Power: 37

Code:

delete from table1
where column_z not in (select column_x from table2)

September 16th, 2004, 06:14 PM

confuzed2xover

Contributing User

Join Date: Apr 2004

Posts: 185

Time spent in forums: 1 Day 5 h 45 m 53 sec
Reputation Power: 5

delete from one table that requires variables from two tables over

Looks like the delete worked where the User ID did not exist- Thank you

Now the mind bender (or at least for me)........

I have three tables. I need to delete the rows in Table1, based on status in Table3. Here how they are tied together.

Table1 (The table to have the rows deleted from)
Column_1a (User ID)

Table2 (Holds User ID and Company ID the user is associated with)
Column_2a (User ID)
Column_2b (Company ID)

Table3 (Company Info & Status)
Column_3a (Company ID)
Column_3b (Status) 0 = Inactive (Inactive Company information is what I want to delete from Table1)

I have tried INNER JOIN with EXISTS, I also tried the original query I posted above and many other variations, still nothing or I delete the entire table (sure glad I am doing this on a test db first).

This was my final attempt:

Code:

DELETE FROM dbo.Table1
WHERE EXISTS (SELECT     Table3.Column_3b
FROM         Table1 INNER JOIN
                      Table2 ON Table1.Column_1a = Column_1a INNER JOIN
                      Table3 ON Table2.Column_2b = Table3.Column_2a
WHERE Table3.Column_3b = 0)

Am I on the right path, or am I totally off now?

Last edited by confuzed2xover : September 16th, 2004 at 07:21 PM. Reason: I forgot the second issue - a bit more complex

September 17th, 2004, 06:41 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,766

Time spent in forums: 2 Weeks 4 Days 6 h 24 m 8 sec
Reputation Power: 37

There are different ways, e.g.

Code:

DELETE FROM dbo.Table1
WHERE column_1a in (SELECT column_2a from table2
INNER JOIN Table3 ON Table2.colum2b = Table3.column_2b
WHERE Table3.column_3b = 0)

when using exists you should use a correlated query, i.e. a reference to a table outside the query

Code:

DELETE FROM dbo.Table1
WHERE EXISTS (SELECT  *
FROM Table2 
INNER JOIN Table3 ON Table2.Column_2b = Table3.Column_2a
WHERE Table3.Column_3b = 0
and table2.column_1a = table1.column_1a)

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: