SQL question. Almost, almost solved. just a nudge would be helpful. :)

[LeeTJ]

I do almost nothing with SQL. i can do the simple select, delete,update and insert commands.

anyway, here is my problem. i have a database where there are many instances of a given foreign key.

is there a way, i can delete all repeating instances of a given foreign key one record for each given foreign key?

if it isn't simple then disregard this post. but if it can be done reasonably simply, would someone try to explain it to me??


thx.

 

[calpha]

Just plain ol SQL, with having no other discernable column then the foreign key......NO.
By discernable I mean data wise....is there other data that makes a record in the foreign key table unique other then the foreign key?

If you can find antoher query-able column (see disceernable description above), then yes......say, like a timestamp, or even an autogenerated ID (autonumber field)......

Delete from table where <foreign key= value> and <timestamp> > Min(TimeStamp)

Depending on your SQL ( I suspect it's access)....the Min Command may require instead to be a part of the "Having Clause".....so to avoid confusion, I'd change the "Min (timestamp)" to be an actual value.

Also:
In higher level SQL where you can program a Stored Procedure with extra variables, then you wouldn't need another limiting factor....you could use a cursor and delete the rest of it, but that wouldn't be simple......

 

[MonkeyK]

I'm not sure what you mean by "many instances of a given foreign key". Do you mean that the same foreign key is used in many tables? That there are many records within the same table that have a foreign key with the same value?

And then are you trying to delete all instances of that foreign key? All but one? Delete all leaving the master intact?

-Mike

 

[LeeTJ]

Table

Primary Key Foreign Key Data1 data2
001 Mike ... ...
002 Mike ... ...
003 Mike ... ...
004 Mike ... ...
005 Jerry ... ...
006 Jerry ... ...
007 Jerry ... ...


So each instance of mike is the same mike in the demographic table with different records here.
same for jerry.

i want to be left with only one record for jerry and one record for mike.

 

[BCYL]

delete from Table where Foreign key = 'Mike' and Primary Key > (select min(primary key) from Table where Foreign key = 'Mike' );

 

[calpha]

you don't have to do an aggregate sub-query

Just use the having clause

Delete from table where foreign key = 'key' having (primary key <> min(primary key))

at least I think you can.

Gonna go try it out on SQL Server now and see if I'm smoking crack. Will update 🙂

 

[LeeTJ]

Originally posted by: BCYL
delete from Table where Foreign key = 'Mike' and Primary Key > (select min(primary key) from Table where Foreign key = 'Mike' );

ok.

now can i use a wildcard instead of 'mike'?

i have 5000 different 'mike's with a total of 18,000 records.

 

[LeeTJ]

ok

Calpha's "having" is the way to go.

unfortunately the version of sql that i'm using doesn't allow for a having statement.

here are my options.

IN, NOT IN, LIKE, NOT LIKE, BETWEEN, NOT BETWEEN

so far i'm looking at something like this

delete from <table> where <foreign key> in (select foreign key where primary key < Max(primary key))

any comments??

i tried running it and i'm getting errors.

 

[LeeTJ]

OK

i'm really really close.

if i use the statement:

Select distinct <column> from <table> it returns all the distinct instances from that column.

Now what do i need to do to get it to return the whole record and not just the column?

 

[calpha]

Select distinct <column> from <table> it returns all the distinct instances from that column.

Now what do i need to do to get it to return the whole record and not just the column?

Doesn't work that way.

When you do a aggregate (distinct, count) combined with a general select, you will not return only one instance of the aggregate field (the distinct column) if more then one entry exists for a distinct number, and the general, you'll recieve each instance.

select * from test yields

id Name otherID
----------- -------------------------------------------------- -----------
1 Chris 1
2 Chris 1
3 John 2
4 John 23 2
5 Jackie 2
6 Jackie 4
8 Michael 5

(7 row(s) affected)

select distinct name, id from test
name id
-------------------------------------------------- -----------
Chris 1
Chris 2
Jackie 5
Jackie 6
John 3
John 23 4
Michael 8

(7 row(s) affected)


select distinct name, id from test where name = 'Chris'
and id in (Select max(id) from test where name='Chris')

name id
-------------------------------------------------- -----------
Chris 2

(1 row(s) affected)

 

[Jeraden]

Does it have to be a single SQL statement? Sounds like it might be easier to do it with a little script...

declare
cursor c1 is
select foreign_key, min(primary_key) primary_key
from Table
group by foreign_key;

begin
for c in c1 loop
delete from Table
where foreign_key = c.foreign_key
and primary_key != c.primary_key;
end loop;
end;

So it will go through all foreign keys and delete all but one instance of each foreign key (leaving the one with the lowest primary key).

 

[LeeTJ]

Originally posted by: Jeraden
Does it have to be a single SQL statement? Sounds like it might be easier to do it with a little script...

declare
cursor c1 is
select foreign_key, min(primary_key) primary_key
from Table
group by foreign_key;

begin
for c in c1 loop
delete from Table
where foreign_key = c.foreign_key
and primary_key != c.primary_key;
end loop;
end;

So it will go through all foreign keys and delete all but one instance of each foreign key (leaving the one with the lowest primary key).

I think your right. a script seems to be the only way to go. unfortunately i get the following error:

Your statement must start with CREATE, DROP, SELECT, INSERT, DELETE, ALTER, EXECUTE, or UPDATE

any ideas?

 

[Jeraden]

What type of database are you using? I've only ever worked with Oracle - what I put above should work in sqlplus or whatever other oracle sql program you use. Not sure how to do them on other databases.

 

[MonkeyK]

I will speculate that if Lee got the error message
"Your statement must start with CREATE, DROP, SELECT, INSERT, DELETE, ALTER, EXECUTE, or UPDATE "
that the database is .... MS Access.

 

[LeeTJ]

Originally posted by: MonkeyK
I will speculate that if Lee got the error message
"Your statement must start with CREATE, DROP, SELECT, INSERT, DELETE, ALTER, EXECUTE, or UPDATE "
that the database is .... MS Access.

🙂

nope. Not MS Access. using a database call Advantage Database. I really wish my vendor would switch to MSDE / SQL. MSDE is free to the vendor for 5 clients or less. i don't know why they choose to pay Extended systems (makers of Advantage Database) for client licenses when MOST of the doctors that use our software have fewer than 5 clients / workstations. 🙁