• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

nSiteful Web Builders

Building a Better Web - One Site at a Time.

  • Home
  • About
    • Testimonials
    • Resources
  • Web Sites
  • Online Marketing
  • WordPress Support
    • Customized WordPress Training
    • 60-for-60 Sessions
  • Web Applications
  • Blog
    • Archive Listing Minimalistic
    • Blog Articles Grouped by Category
    • Case Studies
    • General
    • Portfolio
    • Reviews
    • Snippets
    • Techniques
  • Contact Jeff
    • Purchase Retainer Consulting Hours
    • About Retainer Consulting Hours

By Jeff Cohan, February 26, 2014

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

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.

Related Posts

  1. Get From Foreigner (utility function)
  2. Libra: Division of Labor
  3. Hayes Shutters Online Ordering System
  4. Custom Web Form: Contract Generator in PHP & MySQL
  5. Add Dynamic Table of Contents to a Series of WordPress Posts
  • Choose the best match.

Written by Jeff Cohan · Categorized: Snippets · Tagged: Excel, MySQL, SQL

  • Choose the best match.

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar

mailchimp signup

Subscribe to get notified when new articles are published. Unsubscribe any time. No spam. I promise. Check out my newsletter archives.

social

Twitter Facebook LinkedIn

Recent Articles

  • Use Case for Custom Post Type: “In The News” March 10, 2023
  • Create a Custom Shortcode to Display a MemberPress Membership Price ANYWHERE on Your Website February 5, 2023
  • Avoid Direct Styling; Use CSS Instead September 21, 2022
  • Blog Tags: What They Are (and What They’re Not) August 5, 2022
  • How to Create a Simple Custom Events Plugin May 24, 2022

Filter By Category/Tag

Categories

  • Case Studies (7)
  • General (61)
  • Portfolio (5)
  • Reviews (12)
  • Snippets (16)
  • Techniques (38)

Popular Tags

Advanced Custom Fields Blogging Child Themes Content Marketing CSS CSS Grid Customer Service Custom Fields Custom Post Types Diagnostics Facebook FooGallery Genesis Gutenberg HTML Images iPhone Libra Live Chat Marketing Media MemberPress MemberPress Courses mu-plugins MySQL Photo Gallery php Pinterest Plugins Post Formats Pricing Project Management Security SEO Seth Godin Shortcodes Social Networking Surveys Taxonomies Trello Twitter Video Web design Web forms WordPress

siteground wp hosting

Web Hosting

wp101

EasyWordPresstutorialvideosforbeginners.
MemberPress CTA

Footer

Background

Web Sites | WordPress Support | Web Applications.

Formally trained in liberal arts and education (I have a B.A. in Government from Harvard and studied Secondary Education at Rutgers Graduate School), I have honed my skills in the communication arts and sciences as a teacher, trainer, instructional designer, writer, photographer, calligrapher, helpdesk manager, database programmer, and multimedia developer.

(I've also been a group counselor, waiter, bartender, bicycle messenger boy, computer salesman, carpenter's helper, financial analyst, and school board president.)

Tech

Systems since 1983.
Web sites since 1994.
PHP since 2001.
WordPress since 2007.

Contact

770-772-5134
Email Jeff
Send Money
All Ways

Copyright 2023, nSiteful Web Builders, Inc.

 

Subscribe

Pardon the interruption. I know popups can be annoying. But I’d love to have you as a subscriber.

Sign up to be notified when new articles are published. Unsubscribe any time.

* indicates required

Powered by MailChimp

×