Updating Table Data, Excel VS SQL

Change is part of life and our data is extremely susceptible to these changes. Can Microsoft Excel Keep up with all of the changes to your data? Maybe it’s time to use SQL to update your data.

Let’s discuss updating data in an effective way.

Up until now, many of the tutorials had examples where returns from using SQL over Excel were marginal. Excel is not well suited to update data without the help of VBA or some crazy plugins.

If you are an extreme excel user feeling your blood pressure rising over that last statement, just hear me out a second.  Or send me a note if you have a better way.

IS IT OK TO MANAGE DATA IN EXCEL?

To change values in an Excel spreadsheet, Find and Replace is the best option (that I know of). Find and Replace has its limitations, including:

  • Very Slow with lots of records
  • Criteria for search is limited
  • No Audit trail

Despite the shortfalls listed above, many users insist on managing all their updates in a spreadsheet. This isn’t always a terrible idea. depending on the circumstances, it might be.

Your current situation may make Excel a sufficient tool for data Management. However, data manipulation is an area where a strong foundation of SQL will give you an upper hand.

Here are factors that make Excel data management acceptable:

  • Your access or knowledge of Databases is lacking. (Keep reading Excel2SQL Tutorials!)
  • You don’t have a dependable DBA (Keep reading Excel2SQL Tutorials and do it yourself!)
  • The amount of data you have is small and localized
  • Infrequent or small Number of Changes to data
  • Not dealing with Sensitive Data

MOVING TO SQL TO MANAGE YOUR DATA

After enough working knowledge of SQL under your belt, you will be able to determine the best tool for your circumstance. First, you’ll need that SQL foundation to get started.

A brief word of warning…

SQL provides a chance to improve the speed and precision of updates. It also comes with the opportunity to make a huge freakin mess if you don’t know what you are doing.

I’m here to guide you to that place where you know what you are doing. In another tutorial, you will learn SQL controls to protecting data from yourself and other nefarious users.

UPDATE SCRIPT DEMONSTRATION

We will use the sample data below for all of our practice scripts. The full table has 43 rows.

Let’s pretend all the Pencils in our inventory were actually Pens.

We could easily accomplish this in excel with a simple find and replace

UPDATE CELLS IN EXCEL

It’s good practice to preview rows affected before making any changes.

=COUNTIF(D3:D45,”Pencil)

13 Rows Returned by this COUNTIF formula.

A filter on “Item” would also allow us a sneak preview of changes.

Let’s make the update.

Select the “Item” column and Highlight all the cells in the column.

**Ctrl + Shift + Down**

Now Find and Replace. **Shortcut** Ctrl + H

13 Rows Updated! Just like we expected.

Let’s accomplish the same thing in SQL.

UPDATE ROWS IN SQL

Stick with the practice of regularly previewing our changes. The stakes are even higher in SQL without an undo button. This reminds me of a future conversation we will have.

You remember how to write a select statement right?

SELECT * FROM  dbo.Inventory2
Where Item = ‘Pencil’;

OK, thirteen rows! Time to Update the Data.

The updated Statement has this format:

Update Table
Set Column = ‘New Field Value’
Where Column = ‘Old Field Value’;

Here’s the real-life execution.

Pretty simple right? And the matching row counts should provide some assurance.

UPDATE ROWS BASED ON MULTIPLE CONDITIONS

Now, what if you have to replace values based on multiple conditions.

Here’s our new scenario:

One of our Reps, Jones gets authorized a 10% discount on all Sales. Let’s also pretend the inventory table is a Sales Table!

UPDATE ROWS IN EXCEL BASED ON CRITERIA IN ANOTHER CELL

In Excel, begin by filtering on the Rep “Jones”. Then calculate the 10% off by Multiplying the Unit Cost by .90 in a separate cell. Paste the results of the Calculation as Text over the old Unit Cost.

**If I was using this table long term, I would add a discount field and make “Total” a Calculated field based on Units, UnitCost and Discount.

Wait! You have to make sure the rows are Sorted by Rep to Copy and Paste correctly.

Updating the Data in Excel was not terribly painful in this situation. However, it’s becoming more cumbersome.

What happens if we add some more complexity to our Update requirements.

WHAT IF THE UPDATE SCENARIO BECOMES MORE COMPLEX?

Rep Jones has authorized a 10% discount only on Binders, only for customers in the East Region.  Also, the total has to reflect the new amount. Again, pretend the Inventory table is a Sales table(for me please, I don’t want to update the screenshot and examples)!

**A static Total field would be a lame design in an inventory or Sales system. A real-life table would definitely make this a calculated field.

MORE COMPLEX UPDATES IN EXCEL

In Excel you could do multiple sorts or calculate based on If statements, but do you want to go through all that? I don’t even feel like trying.

Let’s just do it in SQL!

UPDATING MULTIPLE FIELDS BASED ON MULTIPLE CONDITIONS IN SQL

Data manipulation in SQL is a breeze.

Our Scenario Again:

Rep Jones has authorized a 10% discount only on Binders, only for customers in the East Region, and reflect the Update in the Total.

Here we go again!

Always start by previewing changes with a select statement.

Select * from dbo.Inventory2
Where Region = ‘East’ AND  Rep = ‘Jones’  AND  Item = ‘Binder’;

The select statement states that 3 Rows will be affected.

Copy and Paste your Select statement. You will revisit the Select Statement after running the update.  Then convert the copied Select Statement to an Update Statement.

To convert a select statement to an update statement, Replace the “SELECT * FROM” with “UPDATE”.  Then insert the set conditions between the Update and Where Clause. Keep the where as is.

Select * from dbo.Inventory2
Where Region = ‘East’ AND  Rep = ‘Jones’  AND  Item = ‘Binder’;

Update dbo.Inventory2
set UnitCost = UnitCost * .9, Total = Total *.9
Where Region = ‘East’ AND  Rep = ‘Jones’  AND  Item = ‘Binder’;

Lets run this bad boy.

First, the Select Statement to preview our row count.

Then  proceed with the update statement

Run the Select statement Again to view the changes. You better have made that copy as I told you!!

Looks pretty Nice!

TRACKING CHANGES TO ROWS

Excel doesn’t provide a straightforward Audit trail. SQL makes tracking changes much easier.

Logging details on changes made to your data is a great practice.

Excel is lacking in Audit capabilities unless you save a copy of the spreadsheet every time you make a change.

TRACKING CHANGES TO ROWS IN SQL

Let’s lead by example again.

Now we will add a “Time_stamp” to identify the time a record was last updated and “LastModifiedBy” to figure out “Whodunit?”.

We will set the “Time_stamp” equal to the current timestamp of the system. You can also put a static value like a note. “SqlCorrectionQ3Sale” might indicate all rows with this note had a correcting adjustment in the 3rd quarter sale.

A row version is a great addition, but too much for today’s tutorial

Here is our final script that Updates 3 rows and leaves an audit trail.

Tell me a simple way to do this in Excel and I will be really impressed.

keepersecurity.com

Latest Blog Posts

Learn about SQL joins using Excel

Learn about SQL joins using Excel

Why do we join tables in SQL? Tables that logically represent information are the foundation of relational databases. The ancient rules of database normalization remind us to arrange database tables to reduce redundancy and increase flexibility. An adequately...

Super Bowl Squares Template

Updated for Superbowl 57 This Version is unlocked, so be careful with the formulas. You have to press F9 to Regenerate the squares because calculations are set to manual. This spreadsheet is for educational purposes only. Please check your local gambling restrictions...

How to Return Multiple Matches in one Cell Using a lookup?

VLOOKUP is great for looking up a value and returning another corresponding value. But what happens when the first match just isn't enough. I've been tasked with finding a way to put return not just the first but the second, and third match. At first, I tried to...

How to Delete every other Row in Excel

How to Delete every other Row in Excel

Have you ever needed to delete every other row in Excel? You are not alone.   There are a few ways to accomplish it. It's a pretty good candidate for a VBA solution, but I'm going to show you how to quickly delete every other row without using VBA or a plugin. I...

Select the Email Service Provider that is right for you.

Select the Email Service Provider that is right for you.

  Excelshortcut.com commits to continuous improvement. I've wondered if our Communication distribution process is lacking. Is there a more effective tool to help us share valuable content? Mailchimp has been ExcelShortcut.com's tool for email creation and...

Planning Software

Fiverr Freelancers

Share This