The trick is to do a "GROUP BY" on your identifier (name)
and then use a HAVING clause to see if the count is more then 1.
NOTE: You likely need a query that does subqueries that use group by
considering you want to ignore SOME of the records (ie one per group if
that group does not have a status 1 record) but not others (update all
in the group if the group has a status 1 record).
Hopefully that's enough of a hint, but if not when I get a moment I can
spell it out in more detail.
NOTE: I recommend running a SELECT first, rather then an UPDATE, so you
can see what WOULD be updated and verify your query is going to do what
you want before you clobber data.
(or use a transaction, but if its a live database you don't want a
transaction around locking users out)
Terry
Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
Andreas wrote:
> Hi,
>
> I'd like to update some records in a table.
> Those have a status_id and among other columns a varchar with a name
> and a create_date.
> The status_id is 0 if nothing was done with this record, yet.
>
> For some reasons I've got double entries which I now want to flag to
> -1 so that they can be sorted out without actually deleting them since
> there are other tables referencing them.
>
> From every group that shares the same name all should get status_id
> set to -1 where status_id = 0.
>
> The tricky bit is:
> How could I provide, that 1 of every group survives, even then when
> all have status_id = 0?
> Sometimes 2 of a group are touched so both have to stay.
>
>
> e.g.
> c_date, status_id, name
> 2008/01/01, 0, A --> -1
> 2008/01/02, 1, A --> do nothing
> 2008/01/03, 0, A --> -1
>
> 2008/01/01, 0, B --> do nothing (single entry)
>
> 2008/01/01, 0, C --> do nothing (oldest 0 survives)
> 2008/01/02, 0, C --> -1
>
> 2008/01/01, 1, D --> do nothing
> 2008/01/02, 1, D --> do nothing
>
>
>
