Archives

Updating a SQL Table From Another Table (Fixing Your Salesperson Admin Mistake)

The Problem

Yesterday I demonstrated how to use T-sql to update the Customer Table (SLCDPM) to avoid hours of manual labor. I mentioned that you should always make a backup of the table before running an update statement in case you’ve made an error.

One of my readers, Kim, astutely asked me how to use the backup table to fix said error. Well, the process is fairly simple. In this example, I’m working with the M2M Educational Database.

Let’s say that Luke Skywalker (initials LS) was hired as a new Salesperson at your company so you duitifully add him to the SLPN screen. Luke is replacing Chris Kelso (initials CK), who left to join the Galactic Empire.

Let’s say that you created the slcdpm_backup table that I suggested earlier but you made a mistake in your update statement. You were high on Glitterstim and day dreaming about a Twi Lek dancing girl and accidentally used Anakin Skywalker’s initials instead.

UPDATE slcdpm
SET FSALESPN = 'AS' -- NOT LS
WHERE fsalespn = 'CK'

Let’s further assume that you didn’t document the exact update statement used. A week later one of your Salespeople reports errors while trying to save customers and you realize that you’ve made some kind of mistake with the Customer table.

Ah hah you think, “I’ll just restore from the backup table.” Not so fast nerf herder, we need to investigate the changes so that we don’t make problems worse.

The Solution

The first thing you should do is compare your current customer table to the backup. Remember that in this example we can’t remember the exact update statement ran a week ago. Otherwise we would just run it with the correct initials. The following script will show you differences in your tables by comparing the current table with the backup via the identity_column.

SELECT SLC.fcompany
      ,SLC.fcustno
      ,SLC.fsalespn NewSP
      ,BAK.fsalespn OldSP
      ,SLC.identity_column
      ,BAK.identity_column
FROM slcdpm SLC
full outer join slcdpm_backup BAK on BAK.identity_column = SLC.identity_column
where SLC.fsalespn <> BAK.fsalespn or BAK.fsalespn is null or SLC.fsalespn is null

You may want to use an inner join and remove the null statements from the where clause if you’re only interested in columns which have different values. The script above also shows records which exist in one table but not the other in addition to those that have changed. Anyway, the results may look like the grid below. The new records are circled in red while the changed records are in blue.
fixing salesperson
Now you know exactly how many records need to be changed. Update the current table with the following statement.

update slcdpm
set fsalespn = BAK.fsalespn
from slcdpm SLC
inner join slcdpm_backup BAK on BAK.identity_column = SLC.identity_column
where SLC.fsalespn <> BAK.fsalespn
--and SLC.identity_column not in (3,5,7)

A couple of things to note. First, I’ve used an inner join in my update statement but that isn’t actually necessary here. I’ve compared the tables on their identity_column fields, and in a comparison like this where each table may have values the other lacks, the join will ignore the null values. Also note the commented out portion of the Where clause. If the investigation had shown that in the past week some customer’s salesperson settings were legitimately changed, then you could use the where clause and specify which records should be skipped.

I mentioned in the last article that I should demonstrate how to do this using TOAD but when I started to write it up I was taking so many screenshots that I’ve decided to record a video instead. I’ll have a video up in a few days on how to use TOAD to synchronize tables.

Luckily the manual correction process is fast so Darth Vader won’t get wind of your failures and be forced to deal with you.

Any questions?

Related posts:

1 comment to Updating a SQL Table From Another Table (Fixing Your Salesperson Admin Mistake)

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>