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.
mysql UPDATE with DISTINCT
Moderator: General Moderators
Re: mysql UPDATE with DISTINCT
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
Re: mysql UPDATE with DISTINCT
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
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?
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
Re: mysql UPDATE with DISTINCT
It doesn't matter which one gets the clean flag, there's no time stamps, etc.
Re: mysql UPDATE with DISTINCT
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:
Try using two queries:
Code: Select all
select id from table1 group by prop_name, address, city, state, zipThere are 10 types of people in this world, those who understand binary and those who don't