mysql UPDATE with DISTINCT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

mysql UPDATE with DISTINCT

Post by JakeJ »

I'm trying to combine an update a unique dataset within my table.

table1 has prop_name, address, city, state, zip, dup

I want to update the dup field and set it to "clean" for those records pulled by the following query:

SELECT DISTINCT prop_name, address, city, state, zip from table1;

I can't seem to turn that in to an update query though and I'm unsure of the proper construction.

Thanks in advance for the help.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: mysql UPDATE with DISTINCT

Post by VladSun »

What are you really trying to do? Remove the duplicated records later?
There are 10 types of people in this world, those who understand binary and those who don't
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: mysql UPDATE with DISTINCT

Post by JakeJ »

My situation is as follows, I have a huge number of records, and I need to run a series of queries that will eventually mark records as either clean or duplicate so that when I'm done, I can query just the one of each of the records. I have many records that are similar but are essentially duplicates. I do not want to remove any records at all, just flag the the ones I don't want.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: mysql UPDATE with DISTINCT

Post by VladSun »

So you have many rows with the same "prop_name, address, city, state, zip" values and (I susppose) a different id.
How do you decide which particular "id,prop_name, address, city, state, zip" shoud have a "clean" flag?
There are 10 types of people in this world, those who understand binary and those who don't
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: mysql UPDATE with DISTINCT

Post by JakeJ »

It doesn't matter which one gets the clean flag, there's no time stamps, etc.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: mysql UPDATE with DISTINCT

Post by VladSun »

Not sure whether it can be done in a single query. UPDATE query doesn't allow using the table to be updated in a SELECT subquery.
Try using two queries:

Code: Select all

select id from table1 group by prop_name, address, city, state, zip
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply