Updating specific column and records

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

General SQL Server Forums

New to SQL Server Programming

Updating specific column and records

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2011-09-12 : 15:51:05

Hi Group:

I have a very small problem. I found 50% of the solution. I have two tables.
Table 1- HR
Person ID - Person Name
1 - Maria Castillo
2 - John Smith
3 - Harrison Ford
4 - Mary Joe (disabled)

Table 2- Projects
Project ID - Person Name
1001 - Maria Castillo
1002 - Harrison Ford
1003 - Mary Joe
-------------------------------------------------------------
Here is the comparison between this two table.

INSERT INTO #TP_AuditTask_vs_ldap(project, ia_task_assignee)
SELECT Project, ia_task_assignee
FROM [DRTEXW98VM].ITPM.dbo.PMO_IA_Tasks AS td
WHERE (NOT EXISTS
(SELECT displayName
FROM [DRTEXW98VM].ITPM.dbo.PMO_LDAP_data AS d
WHERE ([displayName] = td.[ia_task_Assignee])))

Result = "1 records"

1003 - Mary Joe-

I need to update this record to = "" and/or Null

---------------------------------------------------------------

I created stored procedure that shows a comparison between the two tables and put it inside a Temp Table.

Summary: The objective is to remove the employees that are not longer part of the company from the Project Table.

Thank You.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-12 : 21:29:00

Well, the code you posted doesn't match the tables you posted at all. But all you need to do is an outer join to find the orphans. Code below is based on the tables you posted.

--UPDATE	p SET [Person Name] = NULL
SELECT	p.*
FROM	Projects p
LEFT JOIN
	HR h
On	h.[Person Name] = p.[Person Name]
WHERE	h.[Person Name] IS NULL;

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2011-09-13 : 11:32:52

Thank You Russell.

On the example for the Temp Table. I already finding the differences. What I need is to update the real Project Table against my finding on the Temp Table. Update those employees that are not longer part of the company and enter Null for them on the Project Table. The Temp Table have the results of all current employees vs. Employees in the Project Table. So, it give me the Employees that needs to be updated in the Project Table.

Thanks,

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2011-09-13 : 12:36:34

I am trying to update the Real Table against this Temporary Table. The query looks something like this. But this one is not working.

Thanks,
---------------------------------------------------------

Select *
FRom #TP_AuditTask_vs_ldap

update a
set a.ia_task_assignee = ''
FROM PMO_IA_Tasks.ia_task_assignee a
INNER JOIN
SELECT b.project, b.ia_task_assignee
From #TP_AuditTask_vs_ldap b
where a.Project = b.Project AND a.ia_task_assignee <> b.ia_task_assignee

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2011-09-13 : 12:45:27

Maybe something similar to this query but this one is also having problems.

__________________________________________________________

Select *
FRom #TP_AuditTask_vs_ldap

update a
set a.ia_task_assignee = ''
FROM PMO_IA_Tasks.ia_task_assignee a
INNER JOIN
SELECT *
From #TP_AuditTask_vs_ldap b
on a.Project = b.Project
AND a.ia_task_assignee = b.ia_task_assignee

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-13 : 16:21:44

Use an OUTER JOIN and null the records without matches.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2011-09-14 : 15:27:45

Ok.Enclosed is Part of the solution for my problem I just missing one part of the puzzel.
----------------------------------------------------------------------
I want to insert the Update of that particular Record/colum in another table. This is stored procedure connected to a front end application. The stored procedure runs everytime someone click and specific page. Causing the Insert to happen everytime someone click on the page. I want to INSERT ONLY, when is a value in the ia_task_assignee field. ONLY when is a value in the Temporary Table. Otherwise, I don't want to do Insert.

If is not Null #TP_AuditTask_vs_ldap.ia_task_assignee
BEGIN
INSERT INTO [PMO_IA_Forum](Project,Comments, created, created_by )
SELECT Project, ia_task_assignee + '' + 'Employee is not longer in the company', GETDATE(), 'Auto Generate by System'
FROM #TP_AuditTask_vs_ldap
End

/* Create a Temp Table */

---------------compare both tables-------------------
Create Table #TP_AuditTask_vs_ldap(
Project nvarchar (255),
ia_task_assignee nvarchar (255),
created datetime,
created_by nvarchar (255))

-----Insert to the Temp Table-------

INSERT INTO #TP_AuditTask_vs_ldap(project, ia_task_assignee)
SELECT Project, ia_task_assignee
FROM Server.ITPM.dbo.PMO_IA_Tasks AS td
WHERE (NOT EXISTS
(SELECT displayName
FROM [DRTEXW98VM].ITPM.dbo.PMO_LDAP_data AS d
WHERE ([displayName] = td.[ia_task_Assignee])))

Select *
FRom #TP_AuditTask_vs_ldap

If is not Null #TP_AuditTask_vs_ldap.ia_task_assignee
BEGIN
INSERT INTO [PMO_IA_Forum](Project,Comments, created, created_by )
SELECT Project, ia_task_assignee + '' + 'Employee is not longer in the company', GETDATE(), 'Auto Generate by System'
FROM #TP_AuditTask_vs_ldap
End

update a
set a.ia_task_assignee = ''
FROM [DRTEXW98VM].ITPM.dbo.PMO_IA_Tasks a
INNER JOIN
#TP_AuditTask_vs_ldap b on a.Project = b.Project And a.ia_task_assignee = b.ia_task_assignee

-- drop temporary tables---
drop table #TP_AuditTask_vs_ldap

---------------------
Thank You all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources