Removing Multiple Record Instances

[ConBran]

Hi again,

Back with another question for you. I'm importing files from one table to another, and i need to run a validation check.

Basically I have a table that may or may not have multiple instances of the same Brochure Code. I have been trying to remove these multiple instances to a seperate table in order to tell the suppliers 'Which is the correct record'. I have been trying to use the GROUP BY clause, but it is not giving me what i want.

There you go, i am looking for a way to remove multiple instances of the same record based on field name BorchureCode so that i can move them to a different table so that I can query the supplier.

As per usual, Any and all suggestions are more than welcome. (you may have guessed by now that I am absolutely terrible at SQL - maybe i should get my firm to pay for a course on it? ;op )

Connor

 

[Katy44]

To find the Brochure codes where there's more than one instance run:

select brochurecode, count(brochurecode)
from <table_name>
group by brochurecode
having count(brochurecode)>1

that will give you all the brochurecodes of the possible multiple instances. You can then set up an import routine to transfer all records with these brochurecodes into a new table.

 

[ConBran]

Thanks Katy44 - this has helped me get the necessary records I need from the IDF table into my 'Multiples' table.

Can similar code be used to then Delete these records from the IDF table? (The IDF table is the original table that i am importing into)

It's just that I will need to run more queries on what is left and if these files are removed completely, it will save me a lot of hassle.

 

[Katy44]

This is a bit of a fiddle, but what I would do is put the list of these things into excel, concatenate them with a comma (use the CONCATENATE keyword) and then copy them back into SQL Query Analyzer or whatever, under

delete from <table_name>
where brochurecode in (<list from Excel>

A bit of tidying (e.g. take the last comma off and replace with a closing bracket) and that's all you need. This will however delete ALL instances of the multiples, it won't leave you one! Also this is abviously a one-off procedure, hope that's what you want, if not you will need to use subqueries - it can be done though,

 

[ConBran]

No bother,

I'll fiddle with soemthing like this for a while and give it a bash. You should be glad to hear that I do want it to delete ALL instances of the multiples as i already have a list of them in another table.

Thanks again, I'll let you know how it all goes.

 

[maswien]

Assume you have the table to be exported: origTable
and 2 tartget tables: newTable1, newTable2

May be you want do this:

insert into newTable1
select t0.* from origTable t0 inner join
(select brochurecode, count(brochurecode)
from <table_name>
group by brochurecode
having count(brochurecode)=1) t1
on t0.brochurecode = t1.brochurecode


newTable1 has all the records that brochurecode is unique.

Then do following:

insert into newTable2
select t0.* from origTable
where brochurecode is not in
(select brochurecode from newTable1 )

newTable2 has all the records that brochurecode is not unique.

 

[ConBran]

Hi all,

Was having a bit of trouble with Katy44's suggestion, however, I decided to try somethign different and it worked and lo and behold, I cam e back to tell you all and mjia has posted the same suggestion - but it gets better because the code posted is 'cleaner' than what i had written and will be easier to maintain.

Thanks for all your help in finishing off this package, I daresay that my supervisor will be very happy.

Connor