views:

33

answers:

1

I have a table with columns: Date, Phone, Name, and Event

I need a query that will first recognize the duplicate phone entry, and then assign the name of whichever one has the earlier date to the event column.

+2  A: 

Use:

UPDATE x
   SET x.event = y.name
  FROM YOUR_TABLE x
  JOIN YOUR_TABLE y ON y.phone = x.phone
  JOIN (SELECT t.phone,
               MIN(t.date) As min_date
          FROM YOUR_TABLE t
      GROUP BY t.phone
        HAVING COUNT(*) > 1) z ON z.phone = y.phone
                              AND z.min_date = y.date
                              AND z.min_date < x.date

But it doesn't address if there are duplicates of the minimum value.

OMG Ponies
That updates the record earlier date, I think @sqlman wants the record with the older dates updated.
LittleBobbyTables
@OMG Ponies - I was kind of confused by your question, but then I realized it looks like he responded after I edited his question, but I assure you, the comment was there first :) Your query updates the records with the later dates, but I thought he wanted the `Name` from the earliest record into the `Event` of the later records, while your query just makes the Event and Name of the record the same. I could be totally wrong on what @sqlman wants though, my brain doesn't seem to be working right tonight.
LittleBobbyTables
@LittleBobbyTales "but I thought he wanted the Name from the earliest record into the Event of the later records" --this is correct
sqlman
@OMG Ponies -- so close! Assuming a table name of `Events`, Query Analyzer gave me a `The table 'Events' is ambiguous.` error (this was in 2005 though), so I changed the update statement to `UPDATE x...` and that seemed to work just fine.
LittleBobbyTables
@OMG Ponies - Huzzah! Works on my test table. The *only* caveat I would add is @sqlman may want to modify the `z.phone = y.phone` portion of the query to strip out `(`, `)` and `-` characters in the phone number if they just want to compare the actual digits if that's an issue, but the core of it works.
LittleBobbyTables
thanks guys, i really appreciate the help!
sqlman
@sqlman: You're welcome
OMG Ponies