Update Statement Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Update Statement Needed

Author

Topic

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-12-09 : 12:06:22

I have a table labeled [Staging].[HRIS_EEMaster] in this table is information on employees, there status with the company and so on. I am using their Personal ID numbers as the key [PersNo]. I have a column that we recently added that displays if the employees termination was Voluntary or Involuntary [Vol_Invol]. It seems that at some point, an HR rep was not careful and missed adding this value to 4 employees. I have attempted to go back to the source to correct this information but too much time has elapsed and I can not edit the data now.

What I want to do is update the [Vol_Invol] field for the 4 employees using the [PersNo] as the key. I need an UPDATE Script that will look at the [Staging].[HRIS_EEMaster], find the [PersNo] for the 4 employees and then update the blank [Vol_Invol] field with "Voluntary" (these 4 were all Voluntary Terms). Each employee has a unique [PersNo].

I will add this script to an existing SSIS Package. This SSIS package runs once a month. The staging table is truncated and then loaded with the new data. There is a second package with a SCD that feeds a master table that is used for reporting.

I want to add the UPDATE script to an SQL Task so that it updates (Corrects) these 4 blanks every time the data is pulled. I do something similar with the another code to correct missing Cost Centers that were removed in error, but for some reason, I cant get it to work with this.

If I need to post the code I used for the other correction please let em know.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 13:29:21

If its a one time miss. Why do you need to do it each time?
Anyways, what you're asking can be done like this


UPDATE t
SET t.[Vol_Invol] = 'Voluntary'
FROM [Staging].[HRIS_EEMaster] t
WHERE t.[Vol_Invol] = ''

Assuming you wont have any other blank values existing for [Vol_Invol] field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-12-09 : 14:05:56

That the reason for the PersNo.
I only need to change the ones that the numbers match.

The reason it's going to be in an SSIS package is because the Data is Truncated every month and reloaded with new Data. This information will then become blank again. To correct this I will add this code to replace the missing value for just these 4 employees.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 22:22:35

quote:
Originally posted by brubakerbr

That the reason for the PersNo.
I only need to change the ones that the numbers match.

The reason it's going to be in an SSIS package is because the Data is Truncated every month and reloaded with new Data. This information will then become blank again. To correct this I will add this code to replace the missing value for just these 4 employees.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

Which numbers? what columns contain them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-12-11 : 11:48:34

I want to set a script that I can enter the [PersNo] and then have it update the blank [Vol_Invol] field.

I only need the 4 updated every time the report runs so it doesn't report as a blank field.
I like your Idea but the report displays a count of Voluntary and Involuntary terminations. The data that is pulled shows both Hires and Terminations so I don't want to effect the other data (they need to stay blank).

These 4 employees are from a previous year and are unaccessable for updating. We have implemented steps in the system to make sure this data is not missed in the future so now it's just a matter of patching this small hole with the right data to make the report display correctly.

Not sure how else to explain it, sorry.

It would be equivalent to this.

IF [PersNo] = 12345 then [Vol_Invol] = 'Voluntary',
[PersNo] = 12355 then [Vol_Invol] = 'Voluntary',
[PersNo] = 12385 then [Vol_Invol] = 'Voluntary',
[PersNo] = 13335 then [Vol_Invol] = 'Voluntary',

I know that's not right but hopefully this will give you an idea of what I'm trying to accomplish.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 12:35:05

[code]
UPDATE t
SET t.[Vol_Invol] = 'Voluntary'
FROM [Staging].[HRIS_EEMaster] t
WHERE t.[Vol_Invol] = ''
AND t.[PersNo] = 12345
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-12-11 : 12:56:29

I got this script to work.

UPDATE [Staging].[HRIS_EEMaster]
SET [Vol_Invol] = CASE [PersNo]
WHEN '2000602' THEN 'Voluntary'
WHEN '2050004' THEN 'Voluntary'
WHEN '2050234' THEN 'Voluntary'
WHEN '2050746' THEN 'Voluntary'
ELSE [Vol_Invol]
END

I am going to try yours as well and see which is more efficient.
Let me know if you see anything wrong with the above script. I would like your input (it's how i'm learning).

Thank you for all your help.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-12 : 11:59:13

quote:
Originally posted by brubakerbr

I got this script to work.

UPDATE [Staging].[HRIS_EEMaster]
SET [Vol_Invol] = CASE [PersNo]
WHEN '2000602' THEN 'Voluntary'
WHEN '2050004' THEN 'Voluntary'
WHEN '2050234' THEN 'Voluntary'
WHEN '2050746' THEN 'Voluntary'
ELSE [Vol_Invol]
END

I am going to try yours as well and see which is more efficient.
Let me know if you see anything wrong with the above script. I would like your input (it's how i'm learning).

Thank you for all your help.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

it will simply iterate through all records in table which is not required. you can simplify this as


UPDATE [Staging].[HRIS_EEMaster]
SET [Vol_Invol] = 'Voluntary'
WHERE [PersNo] IN ('2000602',
'2050004',
'2050234',
'2050746')

so that it just affects those 4 records

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-12-12 : 13:47:06

I would not perform a post update process to set those values - instead, I would modify the source query in the data flow source to set those values up front. That way, they come across already set...

If the source is a stored procedure, even better - change the stored procedure and no changes needed in the SSIS package.

Use a CASE expression to set the value as needed - and wrap it with a CAST if you need a different data type.


CASE WHEN PersNo In ('2000602', '2050004', '2050234', '20507046')
     THEN 'Voluntary'
     ELSE Vol_Invol
 END As Vol_Invol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources