Ave,
> 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.
Could you possibly elaborate on this?
Things I'm trying are still not working out the way or want to, or
efficiently. So still looking for a solution.
Thanks.
On 3/23/07 8:01 PM, "Richard Lynch" <ceo@xxxxxxxxx> wrote:
> 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
>>
>>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
