3

I have a decent-sized table 'countries' with about 10 columns. I recently added a new column to the table 'GDP' and I am entering the information per row using

UPDATE countries
SET GDP=(value)
WHERE name = (country);

I have 200 countries in this table. Is there a better way to update this information?

This project is from Khan Academy. We are asked to select a database and run queries on it. The data was pulled from https://gist.github.com/pamelafox/ad4f6abbaac0a48aa781

I am just making sure that there isn't a more efficient way of adding this information to the existing table 'countries' where the primary key is the name of the country.

David Makogon
  • 69,407
  • 21
  • 141
  • 189
baby_sql
  • 31
  • 2
  • 2
    Where are you getting your GDP by country data from? If it's not already in some computer somewhere, then you must first get into some computer. Then depending on what form the data is in, we can advise on how to get that into a SQL table. – RBarryYoung Apr 04 '22 at 14:28
  • As the current comment suggest, we need to know more. But in general, I'd recommend that you don't filter by name, but by ID instead. But that kind of requires some additional things to be happenining in whatever script you're running. – Stoff Apr 04 '22 at 14:31
  • @Stoff: In a table named "countries", the name of the country had *better* be unique, regardless of whether there's also a unique country id number. – Mike Sherrill 'Cat Recall' Apr 04 '22 at 14:39
  • @MikeSherrill'CatRecall' Ofcource, but would you argue that finding a string is faster than finding an int? – Stoff Apr 04 '22 at 14:42
  • @RBarryYoung I am getting the GDP from the IMF website. The data that I am working on was obtained from the website in my edit above. I am running POPSQL that is logged into MySQL. – baby_sql Apr 04 '22 at 15:00
  • perhaps this helps? https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server or https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql – xQbert Apr 04 '22 at 15:04
  • 2
    @Stoff: In big round numbers, there are 300 countries. The difference in speed between searching for a country name and searching for a country id number will be insignificant in a 300-row table. And the user won't have to look up the country id for "Ghana" or "Austria". (FWIW, ISO 3166 two-letter country codes are smaller than an integer.) – Mike Sherrill 'Cat Recall' Apr 04 '22 at 15:05
  • @MikeSherrill'CatRecall'Oh agreed, but he was asking for more effecient ways, and per definition it is. But yes, country code could indeed be better. All I am saying is that if I have a software which does all of this, the software already knows the id, hence searching for the names isn't the most effective way. No matter. – Stoff Apr 04 '22 at 15:31
  • No. There is no more efficient way, but with less than 300 rows, it doesn't need to be efficient. There's "efficient" for *you*, which would mean writing a script, in say python, to take you raw input data, convert it to SQL statements and run them. – Bohemian Apr 10 '22 at 22:05

0 Answers0