jakebur01 Posted March 24, 2008 Share Posted March 24, 2008 How do I select all the rows from my mysql database where my product column is repeated more than once? Quote Link to comment https://forums.phpfreaks.com/topic/97663-selecting-all-where-repeated/ Share on other sites More sharing options...
BlueSkyIS Posted March 24, 2008 Share Posted March 24, 2008 you mean find records that share the same value in a column with other records? select product, count(product) as cnt from your_table group by product having cnt > 1 order by cnt Quote Link to comment https://forums.phpfreaks.com/topic/97663-selecting-all-where-repeated/#findComment-499710 Share on other sites More sharing options...
jakebur01 Posted March 24, 2008 Author Share Posted March 24, 2008 Here's my problem. I have a database with about 50,000 items. Three hundred of these items have identical part numbers because I am using the manufacturers part number. The column next to the manufacturers part number is my number. I cannot just up and revert to using my unique part number because Google has already indexed a lot of my pages. What I am wanting to do is to select all the items that are being repeated and update my unique part number from the isbn2 column into the manufacturers part number column (isbn). How could I go about doing this? Thanks, Jake Quote Link to comment https://forums.phpfreaks.com/topic/97663-selecting-all-where-repeated/#findComment-499715 Share on other sites More sharing options...
BlueSkyIS Posted March 24, 2008 Share Posted March 24, 2008 not tested: update your_table set isbn2 = isbn where product in ( select product from your_table group by product having count(product) > 1) Quote Link to comment https://forums.phpfreaks.com/topic/97663-selecting-all-where-repeated/#findComment-499720 Share on other sites More sharing options...
jakebur01 Posted March 24, 2008 Author Share Posted March 24, 2008 UPDATE books SET isbn2 = isbn WHERE isbn IN ( SELECT isbn FROM books GROUP BY isbn HAVING count( isbn ) >1 ) MySQL said: Documentation #1093 - You can't specify target table 'books' for update in FROM clause Quote Link to comment https://forums.phpfreaks.com/topic/97663-selecting-all-where-repeated/#findComment-499727 Share on other sites More sharing options...
jakebur01 Posted March 24, 2008 Author Share Posted March 24, 2008 bump... Quote Link to comment https://forums.phpfreaks.com/topic/97663-selecting-all-where-repeated/#findComment-499791 Share on other sites More sharing options...
jakebur01 Posted March 24, 2008 Author Share Posted March 24, 2008 Does anyone know how I could update column isbn2 to column isbn where column isbn is repeated more than once using php? Quote Link to comment https://forums.phpfreaks.com/topic/97663-selecting-all-where-repeated/#findComment-499869 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 Anybody? Quote Link to comment https://forums.phpfreaks.com/topic/97663-selecting-all-where-repeated/#findComment-500105 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.