MySQL : Updating Cells

Question

Hi,

I need some help with a mySQL statement.

I've got a mySQL table that looks like this:

Name Year Score   Address       Zip
Joe  2010   A     NULL          NULL
Joe  2009   B     123 Main St.  03755
Mary 2010   B     NULL          NULL
Mary 2009   C     234 Elm St.   03866

I need to grab the 2009 addresses and zips and put them in their respective 2010 cells. Unfortunately, I've got a lot of names.

The names are distinct-- Joe only refers to one Joe and Mary refers to only one Mary.

I know that it would be better to have a separate table with the addresses. But, I've simplified my example a bit. The table isn't designed perfectly, but the above is a problem that I need to solve.

Any advice?

Thanks, Laxmidi

Answer 1

Putting aside the denormalized nature of this table (which you've recognized), you can use a self-join to accomplish this:

UPDATE mytable a
INNER JOIN mytable b 
    ON a.Name = b.Name 
    AND a.Year = 2010 
    AND b.Year = 2009
SET a.Address = b.Address, 
    a.Zip = b.Zip;