MySQL : Updating Cells

Question

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

Ben Hoffstein · Accepted Answer · 2010-08-23 21:09:17Z

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;

edited Aug 23, 2010 at 21:09

answered Aug 23, 2010 at 20:56

Ben Hoffstein

104k8 gold badges107 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Stephen Over a year ago

Dont you mean a.Address and b.Zip in the SET?

Ben Hoffstein Over a year ago

@Stephen, the "a" alias refers here to the 2010 records (which are the ones to be updated). Not sure what you mean.

Stephen Over a year ago

@Ben Your original statement said SET Address = b.Address, Zip = b.Zip - i.e. it referred to generic addresses and zips. My comment was misleading though, I typoed on the 'b.zip' - should have been 'a.zip'

Laxmidi Over a year ago

Hi Ben, Thank you for the message. I really appreciate it. I tried your suggestion, but I got: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM tester2 a INNER JOIN tester2 b ON a.Name = b.Name AND a.Year =' at line 4 I named the table tester2. Any advice on what I'm doing wrong? Thank you! -Laxmidi

Ben Hoffstein Over a year ago

Stephen/Laxmidi - Thanks for the comments. I updated my answer accordingly.

Collectives™ on Stack Overflow

MySQL : Updating Cells

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related