Update for a table - 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 2005 Forums

Transact-SQL (2005)

Update for a table

Author

Topic

Yama
Starting Member

4 Posts

Posted - 2009-02-08 : 05:43:48

Hi,

I have two tables : employee and departments.

In employee table I have a field emp_dep, which contains both name of department and number of department(4 numbers), ex. "HR 4320". Based on this field I have to populate departments table which has the following structure :

department_number (here from my example would be 4320)
department_name (here from my example would be "HR")
manager
division

On a daily basis, an update based on employee table, with the new departments which are not already in departments table, has to be made. As I don't have there a corespondent in departments for emp_dep, is this possible?

Although I would want to, I cannot alter the table structure.

thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-08 : 06:00:24

Yes. Use WHERE NOT EXISTS or LEFT JOIN with WHERE ... IS NULL

E 12°55'05.63"
N 56°04'39.26"

Bodestone
Starting Member

18 Posts

Posted - 2009-02-08 : 15:54:45

Making the assumption that the department number will always be unique then this test script should set you on your way:

--test tables
declare	@employee table(empID INT NOT NULL IDENTITY(1,1), emp_dep nvarchar(100))
declare	@departments table(department_number INT, department_name nvarchar(50))
--test data 1
INSERT	@employee(emp_dep) VALUES('HR 4320')
INSERT	@employee(emp_dep) VALUES('Finance 4321')

INSERT	@departments(department_number,department_name)
SELECT	right(e.emp_dep,4), left(e.emp_dep,len(e.emp_dep) - 4)
FROM	@employee e
WHERE	NOT EXISTS	(SELECT 1
					FROM	@departments
					WHERE	department_number = right(e.emp_dep,4))

--test data 1
INSERT	@employee(emp_dep) VALUES('Bacon Monkeys 4322')
INSERT	@employee(emp_dep) VALUES('GDBs 4323')

INSERT	@departments(department_number,department_name)
SELECT	right(e.emp_dep,4), left(e.emp_dep,len(e.emp_dep) - 4)
FROM	@employee e
WHERE	NOT EXISTS	(SELECT 1
					FROM	@departments
					WHERE	department_number = right(e.emp_dep,4))
SELECT * FROM @departments

Saying that, if you can, get the base structure changed so that you have a departments table as the parent table with a unique ID and the employees table with departmentID as a field foriegn key contrained to the departments table.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-08 : 16:21:06

If emp_dep is not consistent you can use like this. But the performance will be very poor:

SELECT SUBSTRING(EMP_DEP,CHARINDEX(' ',EMP_DEP) + 1,LEN(EMP_DEP) - CHARINDEX(' ',EMP_DEP) + 1),
       SUBSTRING(EMP_DEP,1,CHARINDEX(' ',EMP_DEP))
FROM   EMPLOYEE EP
       LEFT OUTER JOIN DEPARTMENTS DP
         ON DP.DEPARTMENT_NUMBER = SUBSTRING(EMP_DEP,CHARINDEX(' ',EMP_DEP) + 1,LEN(EMP_DEP) - CHARINDEX(' ',EMP_DEP) + 1)
WHERE  DP.DEPARTMENT_NUMBER IS NULL

Bodestone
Starting Member

18 Posts

Posted - 2009-02-08 : 17:23:35

I started with charindex on space but ditched it when adding a department name of 'Bacon Monkeys' since it is specifies in the initial post that department number is always 4 characters/digits.
Mine would fail if this goes up by one but could be rectified to support up to 5 digits by replacing
right(e.emp_dep,4) with ltrim(right(e.emp_dep,5)).
Again there would be a failure if ever there is data that has no spaces between name and number of department. extra spaces can be dealt with by changing
left(e.emp_dep,len(e.emp_dep) - 4) to rtrim(left(e.emp_dep,len(e.emp_dep) - 5))

I am hoping the basic schema will be better before department number moves to 6 figures.

Yama
Starting Member

4 Posts

Posted - 2009-02-09 : 05:02:04

thanks for help. This was really helpful for me.

I know, it's not a good design of tables, but I have to deal with them as they are.

I still have a "small" (I hope) problem.

When inserting into departments, division cannot be null. For this I can insert a value by default, let's say 'N/A'.

The only way I can think of is by adding: " 'N/A' as division ", but I got an error:

Here is the query:(made based on both queries written by Bodestone and sodeep - thanks for it )

INSERT into departments (department_number,department_name,division)
SELECT distinct RIGHT(emp_dep,4) as department_number,
LEFT(emp_dep, LEN(emp_dep)-4) as department_name,
'N/A' as division

FROM EMPLOYEE EP
LEFT OUTER JOIN DEPARTMENTS DP
ON DP.DEPARTMENT = RIGHT(emp_dep,4)
WHERE DP.DEPARTMENT IS NULL

and the error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.departments' with unique index 'IX_Dept'.
The statement has been terminated.

IX_Dept is the index on Department_number.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 05:09:09

this error occurs because the query returns duplicate department number. try the below to spot duplicate number values


SELECT RIGHT(emp_dep,4) as department_number
FROM EMPLOYEE EP
LEFT OUTER JOIN DEPARTMENTS DP
ON DP.DEPARTMENT = RIGHT(emp_dep,4)
WHERE DP.DEPARTMENT IS NULL
GROUP BY RIGHT(emp_dep,4)
HAVING COUNT(*)>1

Yama
Starting Member

4 Posts

Posted - 2009-02-09 : 07:12:35

visakh16 you are right...

It's awfull, I have several duplicate names for departments and same number :((

It is possible an update only for those which are not duplicate?

thanks.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-09 : 09:03:36

quote:
Originally posted by Yama

visakh16 you are right...

It's awfull, I have several duplicate names for departments and same number :((

It is possible an update only for those which are not duplicate?

thanks.

INSERT into departments (department_number,department_name,division)
SELECT  distinct RIGHT(emp_dep,4) as department_number,
LEFT(emp_dep, LEN(emp_dep)-4) as department_name,
'N/A' as division

FROM EMPLOYEE EP
LEFT OUTER JOIN (select DEPARTMENT ,count(*)  from DEPARTMENTS group by DEPARTMENT  having count(*)=1 )DP
ON DP.DEPARTMENT = RIGHT(emp_dep,4)
WHERE DP.DEPARTMENT IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 10:05:56

[code]INSERT into departments (department_number,department_name,division)
SELECT RIGHT(emp_dep,4) as department_number,
MIN(LEFT(emp_dep, LEN(emp_dep)-4)) as department_name,
'N/A' as division
FROM EMPLOYEE EP
LEFT OUTER JOIN DEPARTMENTS DP
ON DP.DEPARTMENT = RIGHT(emp_dep,4)
WHERE DP.DEPARTMENT IS NULL
GROUP BY RIGHT(emp_dep,4)
[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-10 : 05:05:30

quote:

Originally posted by visakh16

INSERT	into departments (department_number,department_name,division)
SELECT RIGHT(emp_dep,4) as department_number,
MIN(LEFT(emp_dep, LEN(emp_dep)-4)) as department_name,
'N/A' as division
FROM EMPLOYEE EP
LEFT OUTER JOIN DEPARTMENTS DP
ON DP.DEPARTMENT = RIGHT(emp_dep,4)
WHERE DP.DEPARTMENT IS NULL
GROUP BY RIGHT(emp_dep,4)

He said he wanted to run an update for only those cases where there are no duplicates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 06:01:36

then just do


UPDATE d 
SET d.field==...

FROM departments d
JOIN(
SELECT RIGHT(emp_dep,4) AS department_number
FROM EMPLOYEE EP
LEFT OUTER JOIN DEPARTMENTS DP
ON DP.DEPARTMENT = RIGHT(emp_dep,4)
WHERE DP.DEPARTMENT IS NULL
GROUP BY RIGHT(emp_dep,4)
HAVING COUNT(*)=1
)tmp
ON tmp.department_number=d.department_number

Yama
Starting Member

4 Posts

Posted - 2009-02-11 : 04:44:57

I got it working by modyfing a little, sakets_2000's query- thanks sakets!

INSERT into departments (department_number,department_name,division)
SELECT distinct RIGHT(emp_dep,4) as department_number,
LEFT(emp_dep, LEN(emp_dep)-4) as department_name,
'N/A' as division

FROM EMPLOYEE EP
LEFT OUTER JOIN DEPARTMENTS DP
ON DP.DEPARTMENT = RIGHT(emp_dep,4)
WHERE DP.DEPARTMENT IS NULL
GROUP BY department_number,department_name,emp_dep
HAVING count(*)=1

thanks all for help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources