;>>> 2012/04/03 18:18 +0100, Tompkins Neil
Before sending the table definition, and queries etc, can anyone advise why
my query with four INNER JOIN might be give me back duplicate results e.g
100,UK,12121
100,UK,12121
Basically the query the statement AND
(hotel_facilities.hotelfacilitytyp
Hi
Before sending the table definition, and queries etc, can anyone advise why
my query with four INNER JOIN might be give me back duplicate results e.g
100,UK,12121
100,UK,12121
Basically the query the statement AND
(hotel_facilities.hotelfacilitytype_id = 47 OR
hotel_facilities.hotelfacilityt
Hello everyone, I would like to ask for idea and help on how to achieve my
concern. Below is my SQL statement. Im joining 2 tables to get my results.
Here's the sample results of what im getting.
Name | Desc | Issue | ATime | Back | TotalTime | Ack | Res
123 | test | error | 2011-10-18 17:09:26
thanks, Ray,
that worked well
(btw, you have a typo, 'Independant' instead of 'Independent')
btw2, I have a pdf with some 15,000 names that I would like to display
with a search function, I email you later, maybe you can help me with that
--
Voytek
> Hi Voytek
>
>
> You could try some vari
Hi Voytek
You could try some variation of:
INSERT INTO inserttable (user, maildir)
SELECT REPLACE(user, '@', 'spam@') as user, CONCAT(maildir,'.spam/')
as maildir
FROM selecttable
[WHERE ..]
the where bit is optional of course!
let me know how you go - hope you are keeping well!
ray
At 03
> Voytek Eymont wrote:
> Are you hoping to do all that you want - copy rows, update rows and
> create new rows - in a single SQL statement? Because if that's what you
> want, I don't think it's possible. Unless someone has come up with some
> new tricks, you can't insert a new record and update
Voytek Eymont wrote:
I have Postfix virtual mailboxes in MySQL table like below:
I'd like to duplicate all records whilst MODIFYING two fields like so:
current record has format like:
user 'usern...@domain.tld'
maildir 'domain.tld/usern...@domain.tld/'
add new record that has:
user 'username
I have Postfix virtual mailboxes in MySQL table like below:
I'd like to duplicate all records whilst MODIFYING two fields like so:
current record has format like:
user 'usern...@domain.tld'
maildir 'domain.tld/usern...@domain.tld/'
add new record that has:
user 'username+s...@domain.tld'
maildir
Ah... Yes. Good point. I like this because I was planning on keeping
the output somewhere for a while. (In case we need an "accounting" at
some point) So it will be easy enough to dump what's being deleted to
the screen while we loop over our candidates.
Thanks!
On Tue, Jul 14, 2009 at 10:16 AM,
That's assuming that there is a unique identifier field, like an auto
increment field. Although that could be added after the fact. Also,
you need to run the query multiple times until it returns no affected
records. So if there are 4 copies of a record, it would need to be run
3 times to get rid o
You can combine the two queries you have in option 3 (you'll need to
change field names, but you should get the idea), something like this:
DELETE table1 FROM table1, (SELECT MAX(id) AS dupid, COUNT(id) AS
dupcnt FROM table1 WHERE field1 IS NOT NULL GROUP BY link_id HAVING
dupcnt>1) AS dups W
@lists.mysql.com
Subject: Removing Duplicate Records
In our database we have an Organizations table and a Contacts table,
and a linking table that associates Contacts with Organizations.
Occassionally we manually add to the linking table with information
gleaned from outside data sources. This is common
s
rebuilding a whole table for a few (hundred at most) offending
duplicate records overkill.
I like option 3 in that it leaves everything as is but does require a
lot of looping and feels inefficient. However, since we'd be running
this only after we do our imports it's not like this looping
That worked Perfect!!!
Thanks a ton!
On 11/8/05 11:27 AM, "Shen139" <[EMAIL PROTECTED]> wrote:
> ALTER IGNORE TABLE mytbl ADD UNIQUE KEY ( Name, email, sex, country ) ;
Rahul S. Johari
Coordinator, Internet & Administration
Informed Marketing Services Inc.
251 River Street
Troy, NY 12180
Tel:
0 AM, "Shen139" <[EMAIL PROTECTED]> wrote:
> ALTER IGNORE TABLE mytbl ADD UNIQUE KEY ( field1, field2,... ) ;
>
> On 11/8/05, Rahul S. Johari <[EMAIL PROTECTED]
> <mailto:[EMAIL PROTECTED]> > wrote:
>>
>> Hi,
>>
>> I used the following S
Hi,
I used the following SQL Query to determine Duplicate Records with same Name
& Email in the row:
SELECT email,
COUNT(email) AS NumOccur1, name, COUNT(name) AS NumOccur2
FROM mytbl
GROUP BY name, email
HAVING ( COUNT(email) > 1 ) AND ( COUNT(name) > 1 )
I want to delete all t
> >
> >
> >
> delete from mytable where record_no=? limit ?
>
> Setting the first parameter to the record number and the second to count
> - 1.
>
Ah "limit", yes that should help.
However, I do not know each individual nos for these "record_no"
Can I have sth that will dynamically delete those rec
Manish wrote:
I have got few duplicate record in my DB as follows.-
Let's say record_no is the unique id of duplicate records.
So I have fields ID, record_no , X and Y. (ID is a dif filed and is a
primary key)
To find duplicates I do following -
select record_no,count(record_no) from mytable
I have got few duplicate record in my DB as follows.-
Let's say record_no is the unique id of duplicate records.
So I have fields ID, record_no , X and Y. (ID is a dif filed and is a
primary key)
To find duplicates I do following -
select record_no,count(record_no) from mytable gro
Fax to:
07/10/2004 05:10 Subject: Is there an easy way to find
duplicate records in a table?
select Vendor, ID, count(*) from ImportTable group by Vendor, ID having
count(*) > 1;
-Original Message-
From: Jeff Gannaway [mailto:[EMAIL PROTECTED]
Sent: Saturday, July 10, 2004 5:11 PM
To: [EMAIL PROTECTED]
Subject: Is there an easy way to find duplicate records in a table?
I hav
I have a table that our distributor sent us. The table doesn't have any
keys. It does, however, have 7,782 duplicate records. I found this out
when I tried to have MySQL make a unique product ID by combining 2 fields
of each record.
Here's what I need to know...
Is there a MyS
sage -
From: "Randy Chrismon"
Sent: Monday, October 13, 2003 9:48 AM
Subject: Disable/Enable Keys and Duplicate Records
I'm not clear on this. What happens to duplicates under the following
scenario?
MyISAM table with either (or both!) a primary key or a unique index.
Alter table
I'm not clear on this. What happens to duplicates under the following
scenario?
MyISAM table with either (or both!) a primary key or a unique index.
Alter table my_table disable keys.
Import 200K records.
Alter table my_table enable keys.
But it turns out there are duplicate records. Does
Let's say I have the following table (t1):
++--++
| id | c1 | c2 |
++--++
| 1 | NULL | 1 |
| 2 | a| 1 |
| 3 | NULL | 2 |
| 4 | b| 2 |
| 5 | NULL | 3 |
| 6 | c| 3 |
| 7 | NULL | 4 |
++--++
I would like to delete all rows that c1 = NULL
At 02:32 PM 7/30/2003, you wrote:
Group,
I have been working on a project for a while now trying to figure out how
to remove duplicate records from a single table using a query. To
complicate matters, what constitutes a duplicate record is a match on
several fields, but not all fields. I
To select records only once try something like
select * from your_table
group by field1, field2, ..
Regards, Paul
Kim Mackey wrote:
> Group,
>
> I have been working on a project for a while now trying to figure out
> how to remove duplicate records from a single table using
D]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 31, 2003 11:41 AM
Subject: Re: Deleting Duplicate Records
> Kim Mackey wrote:
> > Group,
> >
> > I have been working on a project for a while now trying to figure out
> > how to remove duplicate records from a sin
Kim Mackey wrote:
Group,
I have been working on a project for a while now trying to figure out
how to remove duplicate records from a single table using a query. To
complicate matters, what constitutes a duplicate record is a match on
several fields, but not all fields. I have been
how to remove duplicate records from a single table using a query.
To complicate matters, what constitutes a duplicate record is a match
on several fields, but not all fields. I have been successful in
matching all duplicates based on the fields I'm interested in, but
this only return
If all these fields are defined as not NULL, simply
use alter table with the ignore option to add a unique key on all
pertainant fields.
Kim Mackey wrote:
Group,
I have been working on a project for a while now trying to figure out
how to remove duplicate records from a single table using a
Group,
I have been working on a project for a while now trying to figure out
how to remove duplicate records from a single table using a query. To
complicate matters, what constitutes a duplicate record is a match on
several fields, but not all fields. I have been successful in matching
all
f you give more details than we can be
more specific. ;-)
Best regards
Nils Valentin
Tokyo/Japan
2003年 7月 9日 水曜日 15:42、Neil Tompkins さんは書きました:
> Could any one advise what SQL statement I would need to use, to check a
> table for any duplicate records e.g that contain the same data within
Title: RE: duplicate records check
Hi Neil,
try something like this
SELECT
FROM
GROUP BY
HAVING COUNT(*) > 1
Mit freundlichen Grüssen
Frank Kalis
Asset Management
ProACTIV___
CiV Versicherungen * PB Versicherungen * PB Pensionsfonds
Could any one advise what SQL statement I would need to use, to check a
table for any duplicate records e.g that contain the same data within a
field. Note that I haven't got the field as a unqiue field.
Thanks
Neil
_
St
Insert.
PB
-
- Original Message -
From: John Griffin
To: Steve Marquez ; MySQL List
Cc: PHP eMail List
Sent: Friday, June 06, 2003 2:51 PM
Subject: RE: Duplicate records
Hello Steve,
Do a select on the record before you insert it. If the record does not
exist in the
You can always catch the Duplicate Key error and respond accordingly in
your PHP script.
Regards,
Mike Hillyer
www.vbmysql.com
-Original Message-
From: Steve Marquez [mailto:[EMAIL PROTECTED]
Sent: Friday, June 06, 2003 1:41 PM
To: MySQL List
Cc: PHP eMail List
Subject: Duplicate
List
Cc: PHP eMail List
Subject: Duplicate records
Hello.
I am adding a record to a MySQL Database using PHP. I have the records
listed by the field: $id_num.
I want the DB to be updated only with new id numbers, and return an error if
there is a duplicate number already in the DB.
Here is the
Hello.
I am adding a record to a MySQL Database using PHP. I have the records
listed by the field: $id_num.
I want the DB to be updated only with new id numbers, and return an error if
there is a duplicate number already in the DB.
Here is the code I am using:
edit', 'delete', '$id_num', '$titl
Hi!
On Mar 17, mySQL list wrote:
> Below is a minimum script which always results in the error "Got error 127
> from table handler", with only two records in the table. Change the name
> fields from (8 chars) to AAA (7 chars) and no error.
I tried it on both 4.0.11 and 4.0.12.
It, in
tracking this down, or a suggestion for
another way of detecting duplicate records which doesn't cause the problem.
Ian
# ---
USE test;
#
# Table structure for table 'error127'
#
DROP TABLE IF EXISTS `error127`;
Hello,
I need to get a single result from a SELECT from a table with the following sturcture:
component - varchar(25) primary key
catNum - varchar(25) primary key
price - decimal(10,2)
my component list may contain duplicate catNum ie:
primary drive -WD1GJB
- W
Hi everyone,
How do I select a group of records in a table and automatically duplicate
them into the same table while modifying 1 or 2 columns without having to
re-insert each record individually?
Thanks,
Doug
MySQL
-
B
If you use IGNORE in the insert IGNORE into new_table you will get the
result you want.
> -Original Message-
> From: walt [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 30, 2002 3:11 PM
> To: David Kramer; [EMAIL PROTECTED]
> Subject: Re: removing duplicate records
&g
many times in the past, and it works quite well. The only caveat is
the SQL will puke when it tries to load the duplicate records into the
table, I personally would use the DEDUP process of Insert/select with a
group by... But you might want to see what Vivian's response is on the
group by is
> > values contained in the other columns necessary? If you tell me more about
> > what your trying to do and provide some Table DDL I can help you write this
> > query. Just let me know!
> >
> > Thanks,
> >
> > DK
> >
> > group by statement
>
provide some Table DDL I can help you write this
> query. Just let me know!
>
> Thanks,
>
> DK
>
> group by statement
>
> -Original Message-
> From: walt [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 30, 2002 12:43 PM
> To: [EMAIL PROTECTED]
>
statement
-Original Message-
From: walt [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 12:43 PM
To: [EMAIL PROTECTED]
Subject: removing duplicate records
Does anyone know a good way find and remove duplicate records from a table?
I can create an identical table structure and use
If the columns are defined as not null, just add a unique index on all
the fields.
alter table ignore add unique(col1.col2.col#);
walt wrote:
>Does anyone know a good way find and remove duplicate records from a table?
>I can create an identical table structure and use a script to pull r
Does anyone know a good way find and remove duplicate records from a table?
I can create an identical table structure and use a script to pull records
from the existing table and insert them into the new table if they are not
duplicates, but I'd rather not do it that way. Here is an examp
On 20 Jun 2002, at 11:35, Richard DeWath wrote:
> Just a thought on something I was reading with temp tables:
>
> 1. extract the records you want to change the date on from your existing
> table to a temp table.
> 2. update the date in the temp table
> 3. insert the "new" records back into t
ically, or subselects (slated for 4.1?).
>
> -Original Message-
> From: Carlos Fernando Scheidecker Antunes [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 18, 2002 2:40 PM
> To: Don Vu
> Cc: MySQL List
> Subject: Re: How to duplicate records
>
>
> Don,
>
&
Sent: Tuesday, June 18, 2002 9:38 AM
Subject: RE: How to duplicate records
> mabye an insert into...select will work, something like:
>
> INSERT INTO
> SELECT Code, Model, Units
> from
> where Year=2003
> and ((Code = 'N200') or (Code='N205'));
>
cker Antunes [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 18, 2002 10:25 AM
To: MySQL List
Subject: How to duplicate records
Importance: High
Hello all,
I've got some records of a Database that I would like to duplicate if
possible with an statement.
The table has Code, Model, Year, units
Carlos,
Tuesday, June 18, 2002, 5:24:48 PM, you wrote:
CFSA> I've got some records of a Database that I would like to duplicate if
CFSA> possible with an statement.
CFSA> The table has Code, Model, Year, units. The primary key is Code, Model and
CFSA> Year. What I need is to duplicate Code, Mode
Hello all,
I've got some records of a Database that I would like to duplicate if
possible with an statement.
The table has Code, Model, Year, units. The primary key is Code, Model and
Year. What I need is to duplicate Code, Model and units for a different
year.
Say I want to duplicate all recor
Try:
select substring(id,1,8) as pfx from foo group by pfx;
or, if the column is numeric:
select floor(id/100) as pfx from foo group by pfx;
> Date: Tue, 30 Apr 2002 12:59:05 -0700 (PDT)
> From: James Dellacova <[EMAIL PROTECTED]>
> Subject: How do I find du
[snip]
Thank you for the email. Following is the php code
but it doesn't work.
1
";
$result1 = mysql_query($query);
while ($row = mysql_fetch_array($result1))
{
echo "$row[id]";
}
}
?>
[/snip]
Try your $query like this...
$query ="
SELECT id
Thank you for the email. Following is the php code
but it doesn't work.
1
";
$result1 = mysql_query($query);
while ($row = mysql_fetch_array($result1))
{
echo "$row[id]";
}
}
?>
Best regards,
James
--- Jay Blanchard
<[EMAIL PROTECTED]> wrote:
> [sn
[snip]
I have over 68,000 records in a table with unique ID
field that has 10 chars. Need to create a query to
find all matching or duplicate IDs up to 8 chars.
(Eg. 12345678%%)
Can anyone help me?
[/snip]
Try
select ID
from tblFOO
group by ID
having count(*) > 1
HTH!
Jay Blanchard
Applica
Hi All,
I have over 68,000 records in a table with unique ID
field that has 10 chars. Need to create a query to
find all matching or duplicate IDs up to 8 chars.
(Eg. 12345678%%)
Can anyone help me?
Thanks in advance for any comments.
James
_
Dear All,
My record id is 10 char long, all records are unique.
However, many of these ids consist of _. Trying to
print all matching records with first eight characters
of the id. For some weird reason, it's not working
properly. Any comments or help will be greatly
appreciated. Blow is m
That works fine, but I was wondering if there was another way to do it
because I receive one text file everyday and the are always duplicate
records. We just change to MySQL and I have to load this text file everyday
to the database.
Thanks again!!
Nato
-Original Message-
From: Paul
On Mon, Mar 04, 2002 at 04:29:16PM -0500, Natividad Castro wrote:
>
> Thank you very much!! that was very helpful. By the way, is there
> any other way to get rid of duplicate records from a text file?
I can think of others, but they're not nearly as easy or efficient.
Jerem
At 16:29 -0500 3/4/02, Natividad Castro wrote:
>Thank you very much!! that was very helpful. By the way, is there any other
>way to get rid of duplicate records from a text file?
What's wrong with the command Jeremy suggested?
>
>Thanks again
>Nato
>
>-Original
Thank you very much!! that was very helpful. By the way, is there any other
way to get rid of duplicate records from a text file?
Thanks again
Nato
-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 3:00 PM
To: Natividad Castro
Cc: [EMAIL
> I'm new using mysql. I'm trying to load data from a text file to one of my
> table. This text file contains duplicate records. My question is how do you
> tell mysql to delete duplicate records and load just the ones that are not
Why not just filter the data
On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote:
> I'm new using mysql. I'm trying to load data from a text file to one
> of my table. This text file contains duplicate records. My question
> is how do you tell mysql to delete duplicate records and load just
Hi to all,
I'm new using mysql. I'm trying to load data from a text file to one of my
table. This text file contains duplicate records. My question is how do you
tell mysql to delete duplicate records and load just the ones that are not
duplicate? I already tried the REPLACE and IGNORE
ecord (1, 2, 3, ---). I know it's possible to use the
> > DISTINCT operator to show only one of the identical records in a result,
> > but that does not remove one of them from the database.
>
> =Ouch!
>
> =Do you have a particular criteria to delete one or oth
:[EMAIL PROTECTED]]
Sent: Sunday, February 10, 2002 10:31 AM
To: MySql
Subject: Duplicate Records
How does one go about removing one of two identical records in a MySQL
database? My mistake in an earlier database was not applying a unique
number to each record (1, 2, 3, ---). I know it&#
r to each record (1, 2, 3, ---). I know it's possible to use the
> > > DISTINCT operator to show only one of the identical records in a result,
> > > but that does not remove one of them from the database.
> >
> > =Ouch!
> >
> > =Do you have a part
Hi,
You can try to use ALTER IGNORE TABLE syntax :
ALTER IGNORE TABLE your_table ADD UNIQUE(Id);
Regards,
Jocelyn Fournier
- Original Message -
From: "Rich" <[EMAIL PROTECTED]>
To: "MySql" <[EMAIL PROTECTED]>
Sent: Sunday, February 10, 2002 5:31 PM
Su
ical records in a result,
> but that does not remove one of them from the database.
=Ouch!
=Do you have a particular criteria to delete one or other of any duplicate records, or
are they absolutely
identical (and therefore it doesn't matter which stays/goes)?
=You cannot really risk automate
How does one go about removing one of two identical records in a MySQL
database? My mistake in an earlier database was not applying a unique
number to each record (1, 2, 3, ---). I know it's possible to use the
DISTINCT operator to show only one of the identical records in a result,
but that
avoid duplicates.
Regards,
Dan
> -Original Message-
> From: DL Neil [mailto:[EMAIL PROTECTED]]
> Sent: Monday, 11 February 2002 8:30 a.m.
> To: Rich; MySql
> Subject: Re: Duplicate Records
>
>
> Rich,
>
> > How does one go about removing one of two identica
Hi,
You can try to use ALTER IGNORE TABLE syntax :
ALTER IGNORE TABLE your_table ADD UNIQUE(Id);
Regards,
Jocelyn Fournier
- Original Message -
From: "Rich" <[EMAIL PROTECTED]>
To: "MySql" <[EMAIL PROTECTED]>
Sent: Sunday, February 10, 2002 5:31 PM
Su
ical records in a result,
> but that does not remove one of them from the database.
=Ouch!
=Do you have a particular criteria to delete one or other of any duplicate records, or
are they absolutely
identical (and therefore it doesn't matter which stays/goes)?
=You cannot really risk automate
First off,
You would need to add an "id" field, then set it to a primary key, then
set it to "auto_increment"
This will order them correctly
-Original Message-
From: Rich [mailto:[EMAIL PROTECTED]]
Sent: Sunday, February 10, 2002 10:31 AM
To: MySql
Subject: Dup
How does one go about removing one of two identical records in a MySQL
database? My mistake in an earlier database was not applying a unique
number to each record (1, 2, 3, ---). I know it's possible to use the
DISTINCT operator to show only one of the identical records in a result,
but that
Does someone know how to append records ignoring records that do not
meet the database restrictions (UNIQUE index field).
I can do it at the server with the IGNORE clause but that clause
that not exist in access.
TIA,
Dante
-
B
Is there a way to formulate the SQL from MS_ACCESS to allow me to ignore
duplicate records &
insert the others? Or is there a switch on mysqld or myodbc that will allow
me to do it?
Thanks in Advanced,
Dante
-
Before posting,
That makes sense, sound like I'm redoing in PHP what the UNIQUE column
already does.
So all I need to do is modify my PHP trap using mysql_affected_rows() =
0, retun a error messagu to the used indicating a rcord already exists.
Will try it, THX!
Pete
Paul DuBois wrote:
>
> At 5:46 PM -0500 6/27
At 5:46 PM -0500 6/27/01, Pete Kuczynski wrote:
>Thanks Paul!
>ps works great!
>I modified the database with a UNIQUE column like you recommended
>earlier, this will now help in the PHP script to trap the duplicate
>entry and advise the user of the dup.
But if hostname now has a UNIQUE index on i
Thanks Paul!
ps works great!
I modified the database with a UNIQUE column like you recommended
earlier, this will now help in the PHP script to trap the duplicate
entry and advise the user of the dup.
1
$result = mysql_query($query);
// check if row is returned, if yes error, if
>Hi,
>How would a word a select statment, to search a database for duplicate
>entries in one field.
>
>For example, the fields: device, hostname, IP, comments
>
>I want to find all instances where there my be two devices with the same
>hostname.
>
>Thanks!
>
>Pete
Sir, try the following.
SELECT
Peter,
That comes pretty close to the distinct -thread in this mailinglist.
You might want to try:
SELECT device, count(hostname)
FROM your_table
GROUP BY device
HAVING count(hostname)>1;
This would display all devices with at least 2 (or more) hostnames.
If you always have the same IP address,
At 3:13 PM -0500 6/27/01, Pete Kuczynski wrote:
>Hi,
>How would a word a select statment, to search a database for duplicate
>entries in one field.
>
>For example, the fields: device, hostname, IP, comments
>
>I want to find all instances where there my be two devices with the same
>hostname.
SEL
Hi,
How would a word a select statment, to search a database for duplicate
entries in one field.
For example, the fields: device, hostname, IP, comments
I want to find all instances where there my be two devices with the same
hostname.
Thanks!
Pete
--
___
P
H,
Cal
http://www.calevans.com
-Original Message-
From: Eve Edelson [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 27, 2001 6:15 PM
To: [EMAIL PROTECTED]
Subject: duplicate records, auto-increment problems, question
I am starting out to learn database scripting using PHP/mySQL/Apache on
Win98:
ad record number 17, or something
like that. I tried forcibly setting the record number to 1 using
update, no luck.
2. duplicate records on page reload: only with Windows browser (IE and NN)
If I fill out the form and submit, the page goes away and if
I want to go back to the form and e
ary
key to one of your columns.
"---==[bolMyn]==---" wrote:
>
> Well, it seems like nobody wants to bite the question I posted a few
> days ago about ignoring duplicate records during bulk import. So I
> thought, that maybe answer was so obvious that I could not mis
92 matches
Mail list logo
