Manipulating Data: Update Data

  1. Statistics
  2. Manipulating Data
  3. Group Selection

What happened is that, for some of our albums, we didn’t know what the purchase date was, and when we imported the records, somewhere in the import the data went from empty to zeros. Display those albums with:

select album, artist, purchasedate from albums where purchasedate="0000-00-00"

We did not buy any albums on the first Christmas. We don’t want our unknown dates to affect our minimums, our maximums, or even our counts. MySQL, as it turns out, has a special value that stands for nothing. This is the NULL value. We should replace the zero date with NULL in this case so that albums without purchase dates do not affect date calculations.

update albums set purchasedate=NULL where purchasedate="0000-00-00"

select max(purchasedate), min(purchasedate) from albums

Now, our earliest known purchase date is April 1, 1989. This is most likely the date that we started collecting this information.

  1. Statistics
  2. Manipulating Data
  3. Group Selection