Howto Update duplicates

[jwalling]

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

 

 

 

[btherl]

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

[bubblegum.anarchy]

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.

[jwalling]

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

 

 

[jwalling]

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

[btherl]

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.

[jwalling]

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