If the table is "small", you could maybe do:
delete from foo where id in (select b.id from foo as a, foo as b where
a.id < b.id and a.field1 = b.field1 and a.field2 = b.field2 and ...)
Another option would be to just create a UNIQUE INDEX on the fields
you think "should" be unique, and then your second insert is gonna
fail, and you can just ignore that.
On Fri, March 23, 2007 11:51 am, Satyam wrote:
> Delete from table where id = (select min(id) from table group by
> field1,
> field2 ,... having count(id) > 1)
>
> Id is the unique primary key, fieldn are the fields that can be
> duplicated.
> Each time this runs it will remove one occurence of the all duplicated
> records. You'd have to run it several times until it deletes no more
> records. In MySql, you cannot have the same table in the 'delete'
> and in
> the subquery, thus, you will have to first insert the id's in an
> auxiliary
> table and then delete the records.
>
> Satyam
>
> ----- Original Message -----
> From: "Rahul Sitaram Johari" <sleepwalker@xxxxxxxxxxxxxxxx>
> To: "Mark" <markw@xxxxxxxxxxxxxx>; "PHP" <php-general@xxxxxxxxxxxxx>
> Sent: Friday, March 23, 2007 5:24 PM
> Subject: Re: Add New Records Only!
>
>
>>
>> Ave,
>>
>> "Three: Insert everything and remove duplicates later."
>>
>> Out of the suggested options, this option is sounding the most sane
>> attainable on my end. I don't have a complete grip on how to
>> accomplish
>> this, but certainly sounds feasible. Let me look at ways to achieve
>> this.
>>
>> Thanks!
>>
>> On 3/23/07 11:36 AM, "Mark" <markw@xxxxxxxxxxxxxx> wrote:
>>
>>> Rahul Sitaram Johari wrote:
>>>
>>> As far as I can see, there is probably only three ways to do this:
>>>
>>> One: Make sure your dbase system contains unique primary key
>>> capability,
>>> and
>>> use it to avoid duplicates.
>>> Two: query for the row, if it isn't there insert it. (You'll have
>>> to deal
>>> with concurrency with locking or something)
>>> Three: Insert everything and remove duplicates later.
>>>
>>>> Ave,
>>>>
>>>> Let me explain what I¹m trying to do. Unfortunately it¹s not a
>>>> very
>>>> simple
>>>> Add/Update transaction.
>>>> Basically I gather records from multiple mySQL tables and add them
>>>> to a
>>>> DBF
>>>> (dbase) database. I wrote a code which was able to accomplish
>>>> this
>>>> without any problems and the add_records goes very smooth.
>>>>
>>>> However, I have to modify this program now so that only ³New²
>>>> records,
>>>> i.e., records that have not yet been transferred, are added into
>>>> the
>>>> DBF.
>>>> In other words, any records that are being added, which already
>>>> exist in
>>>> the DBF, should not be added.
>>>>
>>>> If it were just adding records from one mySQL to another mySQL, I
>>>> could
>>>> easily use INSERT IGNORE or the Unique Key fundamental however,
>>>> since
>>>> I¹m adding from multiple mySQL tables into DBF, I¹m not sure how
>>>> to go
>>>> about doing this.
>>>>
>>>> Here¹s my code that takes records from multiple mySQL tables and
>>>> adds
>>>> them
>>>> to a DBF.
>>>>
>>>> // define the array with mySQL Table Names & Identifier
>>>> $tChoice = array(
>>>> "lodispo" => "VB",
>>>> "lodispo_osma" => "ATL",
>>>> "lodispo_osmh" => "HOU",
>>>> "lodispo_osmn" => "NSV",
>>>> "lodispo_osmp" => "PAA",
>>>> "lodispo_osmj" => "JAX",
>>>> "lodispo_osmt" => "TPA",
>>>> "lodispo_dmam" => "MET",
>>>> "lodispo_osmf" => "FTM",
>>>> "lodispo_hfglend" => "GLEND",
>>>> "lodispo_hfmesa" => "MESA",
>>>> "lodispo_hfphenx" => "PHENX",
>>>> );
>>>>
>>>>
>>>> // open DBF in read-write mode
>>>> $db2 = dbase_open($theDBFfile, 2);
>>>> if (!$db2) {
>>>> echo "<BR><BR><STRONG>Fatal Error:</STRONG> Unable to open
>>>> database<BR><BR>";
>>>> exit;
>>>> }
>>>>
>>>> else {
>>>>
>>>> // Let's Run Array Loops!
>>>> foreach ($tChoice as $tblQ => $bxQ) {
>>>>
>>>> // connect to mySQL tables one by one
>>>> mysql_select_db($database_imslead_transfer,
>>>> $imslead_transfer); $query_loDispo = "SELECT * FROM
>>>> $tblQ";
>>>> $loDispo = mysql_query($query_loDispo,
>>>> $imslead_transfer) or
>>>> die(mysql_error());
>>>> $row_loDispo = mysql_fetch_assoc($loDispo);
>>>> $totalRows_loDispo = mysql_num_rows($loDispo);
>>>>
>>>> // write mySql data to Dbf
>>>> do {
>>>> dbase_add_record($db2, array(
>>>> $row_loDispo['phone'],
>>>> $row_loDispo['comments'],
>>>> $row_loDispo['starttime'],
>>>> $row_loDispo['endtime'],
>>>> $row_loDispo['dispo'],
>>>> $row_loDispo['loanofficer'],
>>>> $row_loDispo['verifier'],
>>>> $bxQ));
>>>> } while ($row_loDispo =
>>>> mysql_fetch_assoc($loDispo));
>>>> }
>>>>
>>>> dbase_close($db2);
>>>> }
>>>>
>>>> Any suggestions would be highly appreciated.
>>>>
>>>> Thanks,
>>>>
>>>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>>> Rahul Sitaram Johari
>>>> CEO, Twenty Four Seventy Nine Inc.
>>>>
>>>> W: http://www.rahulsjohari.com
>>>> E: sleepwalker@xxxxxxxxxxxxxxxx
>>>>
>>>> ³I morti non sono piu soli ... The dead are no longer lonely²
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>>
>>
>> --
>> No virus found in this incoming message.
>> Checked by AVG Free Edition.
>> Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date:
>> 22/03/2007 7:44
>>
>>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
