jwalling
-
Posts
4 -
Joined
-
Last visited
Never
Posts posted by 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
-
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
-
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
Howto Update duplicates
in MySQL Help
Posted
Thanks for your code suggestion. I already see some useful tidbits.
John