Deleting Items that don't appear in another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deleting Items that don't appear in another table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-20 : 08:36:43

Chris writes "I have two tables. The original has all of the data, some of which is test data. The new table is structured the same but has only the data we want to keep.

You may say, "Why don't you just keep the new table"? Well, the original table has a column called F_1. All the test items have a value in this column. Problem is, so does some of the items that we want to keep.

SO, some hairbrain put the items we want to keep in another table instead of deleting them. SO what I want to do is delete every item in the original table that has a value in F_1 EXCEPT for the items that are present in the new table.

This may sound simple, but let me put a kink in your chain. The dummies that designed this database didn't use an identity column! The only way to identify a row is by combining both a column called GCRecNum and a date column. These will be unique as a whole.

PLEASE HELP! If you need more info, please email me!
Chris
Windows 2000 Advanced Server
SQL Server 2000"

Nazim
A custom title

1408 Posts

Posted - 2002-05-20 : 08:56:38

Dont look so dumb as you are trying to point. therez no compultion that every table should have a identity field.

quote:

This may sound simple, but let me put a kink in your chain. The dummies that designed this database didn't use an identity column! The only way to identify a row is by combining both a column called GCRecNum and a date column. These will be unique as a whole.

EDITED: Didnt Read your Requiremnts properly

, I actually was tring to give a Rant on your above comment. but cant type a lot (got a bruised finger

). Anywayz cing your last post. Am glad i didnt do it.
--------------------------------------------------------------

Edited by - Nazim on 05/20/2002 10:29:46

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-20 : 09:36:35

quote:
The dummies that designed this database didn't use an identity column! The only way to identify a row is by combining both a column called GCRecNum and a date column.

I swear guys, I did NOT design that table! Wish I did though...

They're not dummies. They understand, unlike countless numbers of people, that identity DOES NOT EQUAL primary key just because Microsoft might push them to use one. And they also recognized that they didn't need an arbitrary key for their tables, the existing data provided a natural primary key. This is a sign of educated database designers. You would do well to learn as much as you can from them.

Edited by - robvolk on 05/20/2002 09:38:10

dddddd
Starting Member

4 Posts

Posted - 2002-05-20 : 09:38:26

I don't think you understand... Please read carefully.

quote:

SO what I want to do is delete every item in the original table that has a value in F_1 EXCEPT for the items that are present in the new table.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-20 : 09:44:13

Easy to fix:

DELET FROM o
FROM originaltable o LEFT JOIN testtable t
ON o.gcrecnum=t.gcrecnum and o.date=t.date
WHERE T.gcrecnum IS NULL AND T.date IS NULL

dddddd
Starting Member

4 Posts

Posted - 2002-05-20 : 10:19:39

quote:

I swear guys, I did NOT design that table! Wish I did though...

You might want to look at the DB before you speak. They have data in one column in one table that coincides with the name of a row in another. How do you join this?


Table1
GCRecNum|IDKey
254     |fd_1
254     |fd_2
286     |fd_2

Table2
GCRecNum|fd_1|fd_2
254     |2.5 |
254     |    |1.5
286     |    |.75

Now how in the world do you join somethin like that?
I'm not putting anyone down, it's just really confusing!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-20 : 10:23:40

eeeeeeeeeeeeeeeeeeeewwwwwwwwwwwwwwwwwwwwwwwww

You're right, that's bad! WOW! You didn't mention THAT before!

OK, they were smoking crack when they designed that one.

If there are no other columns in Table1, I don't even think you need it. Seriously. Can you post the full table structures of the tables? Even if you can't change them, we might find ways to work around some of the more obvious problems.

dddddd
Starting Member

4 Posts

Posted - 2002-05-20 : 10:55:16

Here is the table structure


Inspections
GCRecNum|DateOfInspection|fd_1|fd_2|BonusPoint
GCRecNum: Restaurant ID
DateOfInspection: Goes without saying
fd_1 & fd_2: violations, different number of points can be deducted for each violation.
BonusPoint: Either a 1 or a 0


Comments
GCRecNum|DateOfInspection|IDKey|Comments
GCRecNum: Restaurant ID
DateOfInspection: Goes without saying
IDKey: Supposed to use this code to related to the violation in above table
Comments: Comments made on each violation

Restaurants
GCRecNum|RestaurantName|AddressID
First 2 self-explainitory
AddressID: coinsides with address info in another table which I will not display here

The info I need to pull out is the restaurant with each inspection date. Under each inspection date, I need each violation code, it's points, and the comments that go along with it.

I have a new table structure I have laid out that works really well. I simply want to run a script to import this info into the new table. But it will have to be done each week for a date range.

This is about the toughest database I have ever had to work with!

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-05-20 : 21:58:31

I'm trying to get your point:
Is the problem selecting the information you want into your new table, or deleting the junk from the old one, or both? (May be dependent on each other, you want to delete junk first, then fill new table with real data)

Sarah Berger MCSD

Edited by - simondeutsch on 05/20/2002 22:00:09

dddddd
Starting Member

4 Posts

Posted - 2002-05-21 : 13:04:09

Actually, I got the data deleted, but how to pull the information from the table?

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-05-21 : 22:07:24

What does your new table structure look like? Please explain, as it's unclear how you actually want the data, and therefore, what the problem is.

Sarah Berger MCSD

Nazim
A custom title

1408 Posts

Posted - 2002-05-22 : 02:29:27

Try on these lines

select i.*,c.*,r.*
from inspectins i
inner join Comments c
on i.getrecnum=c.getrecnum
inner join Restaurant r
on i.getrecnum=r.getrecnum
where idkey = case idkey when 'fld_1' then i.fld_1 else i.fld_2 end

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources