Chapter 3, Sql Question

[Dimitri Vorontzov]

Chapter 3 of the 3rd Edition of this book includes some work with a database of US zipcodes borrowed from US census bureau or some similar organization. The database is located here (the smaller database):

 

http://www.federalgo...mentzipcodes.us

 

Some of the latitude and longitude values in the database are empty, which can be checked by running the following query:

 

SELECT * FROM zip_codes WHERE latitude='';

 

Which outputs a number of results.

 

(Showing rows 0 - 29 (648 total, Query took 0.0042 sec))

 

Larry offers to replace the empty values with NULL, by running this query:

 

UPDATE zip_codes SET latitude=NULL, longitude=NULL WHERE latitude='';

 

But nothing happens!

 

(0 row(s) affected. ( Query took 0.0993 sec ))

 

Is there an error in the UPDATE query? What am I missing, why does it fail to update?

 

Would be grateful for your help, as always!

[HartleySan]

Most likely, you haven't made the latitude and longitude values "nullable" in the DB.

In other words, you need to set the columns in the DB so that a NULL value can be set in the first place.

Please verify that.

[Dimitri Vorontzov]

Thank you HartleySan, you nailed it!