SQLTeam.com | Weblogs | Forums

Confused my self updating 1 table from another


#1

Morning all,

I've completely confused my self here, I'm probably coming at it from the wrong angle. I'm trying to update 1 table from another.

Table SMS contains SMS_ID, SMS_Number, SMS_Message, Emp_Name, Emp_ID & Processed.
Table Employees contains Emp_ID, Emp_FName, Emp_SName & Emp_CellNumber.

I'm trying to update the SMS table with Emp_Name & Emp_ID where SMS_Number matches Emp_CellNumber where processed is false.

The (non working) code I have at the moment is:

BEGIN
DECLARE @Emp_ID INT
DECLARE @Emp_Name NVARCHAR(50)
    IF EXISTS (SELECT 1 @Emp_ID = Emp_ID, (Emp_FName + ' ' + Emp_SName) As @Emp_Name = Emp_Name
                FROM Employees
                WHERE Status = 'Live'
                AND REPLACE(Emp_CellNumber, ' ', '') LIKE '%123456789')
    BEGIN 
        INSERT INTO SMS (Emp_ID, Emp_Name)
        VALUES (@Emp_ID, @Emp_Name)
        WHERE Processed = 'False'
    END
END

Any pointers as to where I've gone wrong or a better way to achieve this please?

Thanks

Dave


#2

First off: If [SMS] and [Employees] have some columns in common (e.g. name and CellNumber) then GET RID of those columns from one table (e.g. keep the Name in [Employees] and the [CellNumber] in [SMS]. Otherwise whenever they become different, for whatever reason, you won't know which version to trust.

(That might not be an issue for you, but just wanted to point it out. For more info Google: "First Normal Form")

I see that you are concatenating First and Last name to make the Name in SMS. If you do choose to remove the Name from [SMS] you can use a VIEW for the concatenated name, for example something like this:

CREATE VIEW SMS_WIthName
AS
SELECT S.SMS_ID, S.SMS_Number, S.SMS_Message, S.Emp_ID, S.Processed,
       (E.Emp_FName + ' ' + E.Emp_SName) AS Emp_Name
FROM SMS AS S
     JOIN Employees AS E
        ON E.Emp_ID = S.Emp_ID

If you want to insert new rows into [SMS] for rows in [Employees] table then something like this:

INSERT INTO SMS (Emp_ID, Emp_Name)
SELECT Emp_ID, (Emp_FName + ' ' + Emp_SName)
FROM Employees
WHERE Status = 'Live'
      AND REPLACE(Emp_CellNumber, ' ', '') LIKE '%123456789')
REPLACE(Emp_CellNumber, ' ', '') LIKE '%123456789')

this means "Any cell number ending in "123456789" (ignoring any spaces). Just checking that is what you want?


#3

Hi Kristen,

Thanks for reply. Yeah good point about the column names. Columns are now named:

Table SMS contains:
SMS_ID
SMS_Number
SMS_Message
SMS_Emp_Name
SMS_Emp_ID
SMS_Processed.

Table Employees contains:
Emp_ID
Emp_FName
Emp_SName
Emp_CellNumber

Correct I was concatenating Emp_FName & Emp_SName as the name doesn't need to be seperate in this table.
Correct I was removing all spaces and ignoring the first characters as its either '0' or '+44' depending how its been added.

I think I may have badly explained what I'm attempting to do. Employees has lots of names & phone numbers in it. SMS contains raw incoming text messages.

Basically I'm trying to cross reference the incoming text message number (SMS_Number) with the phone numbers contained in Employees (Emp_CellNumber). If there is a match copy the name & EMP_ID from Employee to SMS and chnage SMS_Processed to True. ie

SMS Table
SMS_ID      SMS_Number       SMS_Message    SMS_Emp_Name       SMS_Emp_ID    SMS_Processed.
1           +441111111111    message 1       NULL              NULL           False
2           0222222222       message 2       NULL              NULL           False
3           +44333333333     message 3       NULL              NULL           False
4           09999999999      message 4       NULL              NULL           False

Employees Table: 
Emp_ID      Emp_FName     Emp_SName      Emp_CellNumber
1000         Bob          Smith           01111 111111
1001         Jane         Doe             02222 222222
1002         John         Brown           03333 333333

Result SMS Table:
SMS_ID     SMS_Number    SMS_Message      SMS_Emp_Name     SMS_Emp_ID    SMS_Processed.
1          441111111111   message 1        Bob Smith        1000          True
2          0222222222     message 2        Jane Dow         1001          True
3          +44333333333   message 3        John Brown       1002          True
4           09999999999   message 4        NULL             NULL          True

Hope thats a better explanation...

Thanks

Dave


#4

I wasn't really meaning that, although personally I prefer that sort of naming convention.

I would not have the NAME in both Employees and SMS table - you can get the name, whenever you want, by JOINing the SMS table to the Employees table.

I can see how that works in a transaction processing sense, and perhaps your SMS_Processed column takes care of it, but the risk exists for your update to assign an Employee-ID to the SMS record and then the CellNumber on the Employee record changes (they buy a new phone) or someone changes the SMS record (to manually correct the Employee ID or SMS_Number. Now the SMS record and the Employee record contain the same data columns in both tables (Employee Name and Cell Number) but they contain conflicting data. Looking at it you won't know which of the two possibilities is the correct one.

I certainly would not put the Employee's Name in the SMS table - that exists in the Employee table. If they get married / whatever and their name changes are you going to update all the historical SMS records? Just store that information in one place.

For me, ideally, if you put the Employee ID in the SMS record (having matched on SMS Number / EMP CellNumber) then you might want to change the SMS Number to NULL - i.e. if there is an Employee ID in the SMS record then there is no longer any need to hold the SMS Number (there may be a need, to know what the SMS Number was AT THE TIME the record was made, but if not don't store it in duplicate, just keep it in one place).

Another approach would be to have a LINK table that joins EMP to SMS based on the Cell Number. The link table would be EMP_ID and SMS_Number. You can then join that to Employees table (to get the name) and to the SMS table (to get the Message etc.)

Why update the SMS table at all? Is there some reason not to just JOIN the two tables at the time that you produce the report?


#5

Hi Kristen,

Thanks fot the reply. I've just read it a few times and you are quite right, you've made a couple of very valid points that I overlooked... It serves no purpose to keep the name in the SMS table!!

I'm wondering if I can simplify the entire process. Is it possible to do the phone number comparison in the insert statement?

Hmmmm this compiles but doesn't seem to work....

DECLARE @SMS_ID nvarchar(20) = 8888
DECLARE @SMS_Number nvarchar(15) = 441234567890
DECLARE @SMS_Message nvarchar(160) = 'text message blar blar'
DECLARE @SMS_Processed bit = 'False'

BEGIN
   IF NOT EXISTS (SELECT SMS_ID FROM SMS 
                   WHERE SMS_ID = @SMS_ID)
   BEGIN
       INSERT INTO SMS (SMS_ID, SMS_Number, SMS_Message, SMS_Processed, SMS_Emp_ID)
       VALUES (@SMS_ID, @SMS_Number, @SMS_Message, @SMS_Processed, (
       
       SELECT Emp_ID
       FROM Employees 
       WHERE Emp_Status = 'Live'
       AND (REPLACE(Emp_CellNumber, ' ', '')) LIKE (REPLACE(@SMS_Number,left(@SMS_Number,2),''))
       ))

   END
END

#6

Doh...! Spotted my typo... The should have been

AND (REPLACE(Emp_CellNumber, ' ', '')) LIKE '%' + (REPLACE(@SMS_Number,left(@SMS_Number,2),''))

Looks like its working now...

Many thanks

Dave