data cleansing table needs dupes removed

#1

my ci2 table: id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates across zones
with the 4 columns in the group by making it unique. I only need to
order 1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*) > 1 )

How to change the select into a delete?

I am reading on how to create a temp table. How do I get the unique
records into the temp table? Then I drop the real table.

tnx,

#2

On 12-11-10 20:15, jr wrote:

Quote:

my ci2 table: id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates across zones
with the 4 columns in the group by making it unique. I only need to
order 1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

How to change the select into a delete?

I am reading on how to create a temp table. How do I get the unique
records into the temp table? Then I drop the real table.

tnx,

i'm sure its somewhere on this page:
http://dev.mysql.com/doc/refman/5.0/en/delete.html

--
Luuk

#3

On 12-11-10 20:36, Luuk wrote:

Quote:

On 12-11-10 20:15, jr wrote:
my ci2 table: id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates across zones
with the 4 columns in the group by making it unique. I only need to
order 1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

How to change the select into a delete?

I am reading on how to create a temp table. How do I get the unique
records into the temp table? Then I drop the real table.

tnx,

i'm sure its somewhere on this page:
http://dev.mysql.com/doc/refman/5.0/en/delete.html

without reading that page,

you could also do:
UPDATE ci2 SET businessunit=12345
WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

(change the '12345' to any businessunitnumber you can recognize as
deletable)

and after that you can do a:
DELETE from ci2 WHERE businessunit=12345

--
Luuk

#4

On Nov 12, 11:52*am, Luuk <L... (AT) invalid (DOT) lan> wrote:

Quote:

On 12-11-10 20:36, Luuk wrote:

On 12-11-10 20:15, jr wrote:
my ci2 table: id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates across zones
with the 4 columns in the group by making it unique. I only need to
order 1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

How to change the select into a delete?

I am reading on how to create a temp table. How do I get the unique
records into the temp table? Then I drop the real table.

tnx,

i'm sure its somewhere on this page:
http://dev.mysql.com/doc/refman/5.0/en/delete.html

without reading that page,

you could also do:
UPDATE ci2 SET businessunit=12345
WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

(change the '12345' to any businessunitnumber you can recognize as
deletable)

and after that you can do a:
DELETE from ci2 WHERE businessunit=12345

--

tnx,

#5

On 12-11-10 20:52, Luuk wrote:

Quote:

On 12-11-10 20:36, Luuk wrote:
On 12-11-10 20:15, jr wrote:
my ci2 table: id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates across zones
with the 4 columns in the group by making it unique. I only need to
order 1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

How to change the select into a delete?

I am reading on how to create a temp table. How do I get the unique
records into the temp table? Then I drop the real table.

tnx,

i'm sure its somewhere on this page:
http://dev.mysql.com/doc/refman/5.0/en/delete.html

without reading that page,

you could also do:
UPDATE ci2 SET businessunit=12345
WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

lol, i should read the UPDATE page more often... ;-)

Quote:

(change the '12345' to any businessunitnumber you can recognize as
deletable)

and after that you can do a:
DELETE from ci2 WHERE businessunit=12345

--
Luuk

#6

On Nov 12, 11:52*am, Luuk <L... (AT) invalid (DOT) lan> wrote:

Quote:

On 12-11-10 20:36, Luuk wrote:

On 12-11-10 20:15, jr wrote:
my ci2 table: id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates across zones
with the 4 columns in the group by making it unique. I only need to
order 1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

How to change the select into a delete?

I am reading on how to create a temp table. How do I get the unique
records into the temp table? Then I drop the real table.

tnx,

i'm sure its somewhere on this page:
http://dev.mysql.com/doc/refman/5.0/en/delete.html

without reading that page,

you could also do:
UPDATE ci2 SET businessunit=12345
WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

(change the '12345' to any businessunitnumber you can recognize as
deletable)

and after that you can do a:
DELETE from ci2 WHERE businessunit=12345

--
Luuk

By the way, none of the business units are deleteable but only the bu,
zonenm,zoneid, ndc as unique rows would be deleteable when there is
more than one.
How do I tag only the ones I want to delete?

#7

On Nov 12, 11:52*am, Luuk <L... (AT) invalid (DOT) lan> wrote:

Quote:

On 12-11-10 20:36, Luuk wrote:

On 12-11-10 20:15, jr wrote:
my ci2 table: id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates across zones
with the 4 columns in the group by making it unique. I only need to
order 1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

How to change the select into a delete?

I am reading on how to create a temp table. How do I get the unique
records into the temp table? Then I drop the real table.

tnx,

i'm sure its somewhere on this page:
http://dev.mysql.com/doc/refman/5.0/en/delete.html

without reading that page,

you could also do:
UPDATE ci2 SET businessunit=12345
WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*)> 1 )

(change the '12345' to any businessunitnumber you can recognize as
deletable)

and after that you can do a:
DELETE from ci2 WHERE businessunit=12345

--
Luuk

Would it be something like select distinct (bu AND zonenm AND zoneid
AND ndc) INTO tbltmp

#8

On Fri, 12 Nov 2010 11:15:55 -0800 (PST), jr wrote:

Quote:

my ci2 table: id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates across zones
with the 4 columns in the group by making it unique. I only need to
order 1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*) > 1 )

How to change the select into a delete?

I am reading on how to create a temp table. How do I get the unique
records into the temp table? Then I drop the real table.

slap an aggregate over all the columns?

--
34. I will not turn into a snake. It never helps.
--Peter Anspach's list of things to do as an Evil Overlord

#9

On Nov 12, 1:25*pm, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:

Quote:

On Fri, 12 Nov 2010 11:15:55 -0800 (PST), jr wrote:
my ci2 *table: *id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates *across zones
with the 4 columns in the group by making it unique. *I only need to
order *1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*) > 1 )

How to change the select into a delete?

I am reading on how to create a temp table. *How do I get the unique
records into the temp table? *Then I drop the real table.

slap an aggregate over all the columns?

--
34. I will not turn into a snake. It never helps.
* * * * --Peter Anspach's list of things to do as an Evil Overlord

Okay, I tried to do as you suggested

select businessunit, zonenumber, ndc, GROUP_CONCAT(id) from ci2
Group by businessunit,zonenumber, ndc

This would be helpful if I got the distinct list of id's from the rows
where the ndc's across zones were more than one for each
businessunit. I'm not sure if I explained it earlier but I don't care
about zoneid's just zonenumbers. I only need distinct ndcs across
zonenumbers for each unit.
thanks again,

#10

On Nov 12, 1:25*pm, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:

Quote:

On Fri, 12 Nov 2010 11:15:55 -0800 (PST), jr wrote:
my ci2 *table: *id,businessunit, zonenumber,zoneid, ndc
There are 874 records that have 1 or more duplicates *across zones
with the 4 columns in the group by making it unique. *I only need to
order *1 per business unit, but the zones have more than one ndc
across the zones.
select * FROM ci2 WHERE ndc is not null
GROUP BY businessunit,zonenm,zoneid,ndc
HAVING ( COUNT(*) > 1 )

How to change the select into a delete?

I am reading on how to create a temp table. *How do I get the unique
records into the temp table? *Then I drop the real table.

slap an aggregate over all the columns?

I am trying to get the distinct rows into the temp table and I get the
error
"Operand should contain 1 column(s)". How do I get the distinct rows
grouped by these 3 columns into the temp table?
thanks,

CREATE TABLE ci2_tmp as
SELECT * FR"OM ci2
WHERE ndc <> NULL
GROUP BY (bu,zonenm,nationaldrugcode)