Update MySQL table using subquery, Excel data

Share this!

Here’s a snippet of SQL code and a procedure for updating a MySQL database table using a subquery and data from Microsoft Excel (or similar). Perhaps you could adapt this solution to your own situation.

Background

Import Data From Excel

My client has a Web-site page which lists and includes hyperlinks to a few dozen product demo videos stored on his YouTube channel. The list is dynamically generated on the Web-site page via PHP scripts that query the MySQL database table I created and initially populated for him (video_listing). Although the table includes a column for description, my client didn’t have any descriptions to give me at the time, and he didn’t think they were necessary — despite my advice that they would be at least helpful.

(Insofar as the list of videos was — and was expected to remain — relatively static, the client didn’t need or want a CMS for populating or editing the table’s data.)

Well, things change.

My original contact at the company has moved on, and my new client contact does want descriptions.

The Solution

I exported the most important columns from the table (id, title, and description) to a CSV file. I sent the file to my client with instructions to fill in descriptions and leave every other column intact. He opened the file in Excel, made his edits, and returned the edited file to me.

I exported the Excel file to CSV and, using phpMyAdmin, I imported it into a temporary empty table (temp_source) whose structure matched the structure of the CSV file.

I then executed the following SQL statement in phpMyAdmin. Note that the statement utilizes a subquery in line #3.

Snippet:

UPDATE video_listing 
SET description = 
(SELECT description FROM temp_source WHERE id = video_listing.id)

Voila!

So much better than copying and pasting from an unstructured email or Word document. Especially if and when the client wants to change the descriptions later on. (Which, by the way, he did!)

Comments and questions are welcome. Share your solution, if it differs from mine. Thanks for listening.

Share this!

About JeffCohan.com

The nSiteful Tech Blog (the official blog of nSiteful Web Builders, Inc. since January of 2013) is where I (Jeff Cohan) and (occasionally) associates will be posting articles of potential interest to like-minded techies, nSiteful clients who are playing active roles in the maintenance of their own Web sites and blogs, and pretty much anyone interested in how Web strategies and tools can help them reach their goals.

This entry was posted in Snippets and tagged , , , by Jeff Cohan. Bookmark the permalink.

About Jeff Cohan

Jeff and his wife, Margie, are the proud parents of Sarah and Jake. Jeff is the founder, president and chief cook and bottle washer of nSiteful Web Builders, Inc., a Web development and Internet Consulting firm. In his spare time, Jeff builds Web sites and Web applications, plays guitar, putters around in his basement woodworking shop, mercilessly spoils his grandchildren, and creates videos from more than two decades of home movies. His current video project is an extended montage of people (mainly family members) asking him to stop filming them.

Leave a Reply

Your email address will not be published. Required fields are marked *