Jump to content

Howto Update duplicates


jwalling

Recommended Posts

Apache version  1.3.37 (Unix)

MySQL version 5.0.27-standard

PHP version 5.2.0

 

I want to update duplicate records in a Drupal database application by adding dupe count in each dupe record based on key [org_name+org_city].

 

I am able to list duplicates with this:

 

[snip]

$result = mysqli_query($db, 'SELECT org_name,org_city FROM content_type_resource GROUP BY org_name,org_city HAVING COUNT(*)>1');

    while ($row = mysqli_fetch_object($result)) {

      printf(

        '<tr><td>%s</td><td>%s</td></tr>',

        htmlspecialchars($row->org_name),

        htmlspecialchars($row->org_city)

[/snip]

 

How can I add an update statement that accomplishes the following logic:

      UPDATE content_type_resource SET org_dupe=count WHERE count > 1;

 

I might be able to use a temporary table, but my knowledge of PHP and MySQL is rudimentary.

 

Thanks in advance,

John

 

 

 

Link to comment
Share on other sites

Just add an update statement within your loop.  Make sure you don't use the same variable name $result for the update :)  The update will just need to specify which org_name and org_city need updating, which you have available from $row.

 

It can be done in a single query as well, which may be useful if efficiency is important.  IF you want to know how to do that, ask :)

Link to comment
Share on other sites

The following information may be relevant to the problem of updating duplicates. I would like to see some example code that works around the Syntax contstraints. -jw-

 

UPDATE Syntax

http://dev.mysql.com/doc/refman/5.0/en/update.html

"Currently, you cannot update a table and select from the same table in a subquery."

 

...

 

Posted by Dewey Gaedcke on December 27 2006 8:34pm

 

Above in the docs, it says "you cannot update a table and select from the same table in a subquery"

 

This is true but there are two simple ways around this limit.

1) nest the subquery 2 deep so it is fully materialized before the update runs. For example:

Update t1 set v1 = t3.v1 where id in

(select t2.id, t2.v1 from (select id, v1 from t1) t2) t3

 

2) use a self join rather than a subquery

 

 

Link to comment
Share on other sites

Just add an update statement within your loop.  Make sure you don't use the same variable name $result for the update :)  The update will just need to specify which org_name and org_city need updating, which you have available from $row.

 

Since my PHP proficiency is limited to adapting examples, would you give one for this suggestion?

 

Thanks,

John

Link to comment
Share on other sites

It can be done in a single query as well, which may be useful if efficiency is important.  IF you want to know how to do that, ask :)

 

I would like to know how since afaik an update can not be made based on a subset of the same table.

 

Maybe it can't.  I'm not an expert in mysql.

 

Here is your example John:

 

$result = mysqli_query($db, 'SELECT org_name,org_city,count(*) FROM content_type_resource GROUP BY org_name,org_city HAVING COUNT(*)>1');
    while ($row = mysqli_fetch_object($result)) {
      $upd_sql = 'UPDATE content_type_resource '
      . " SET org_dupe = {$row->count} "
      . " WHERE org_name = '" . $row->org_name . "'"
      . " AND org_city = '" . $row->org_city . "'";
      $upd_result = mysqli_query($db, $upd_sql) or die("Error in $upd_sql");
    }

 

The code is unfortunately untested.  I hope someone familiar with mysqli can help..  The idea is just to run an update using the count, name and city fetched from the outer query.

Link to comment
Share on other sites

The code is unfortunately untested.  I hope someone familiar with mysqli can help..  The idea is just to run an update using the count, name and city fetched from the outer query.

 

Thanks for your code suggestion.  I already see some useful tidbits.

 

John

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.