Update MySQL table using subquery, Excel data

Last updated October 10th, 2018 at 08:05 am

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

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.