Page 1 of 1

mysql UPDATE with DISTINCT

Posted: Fri Feb 18, 2011 1:39 pm
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.

Re: mysql UPDATE with DISTINCT

Posted: Fri Feb 18, 2011 6:48 pm
by VladSun
What are you really trying to do? Remove the duplicated records later?

Re: mysql UPDATE with DISTINCT

Posted: Sat Feb 19, 2011 8:50 am
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.

Re: mysql UPDATE with DISTINCT

Posted: Sat Feb 19, 2011 10:16 am
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?

Re: mysql UPDATE with DISTINCT

Posted: Sat Feb 19, 2011 7:43 pm
by JakeJ
It doesn't matter which one gets the clean flag, there's no time stamps, etc.

Re: mysql UPDATE with DISTINCT

Posted: Mon Feb 21, 2011 5:39 am
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