Wrong Update - 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 2000 Forums

Transact-SQL (2000)

Wrong Update

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-23 : 08:13:34

I HAVE THE FOLOWING QUERIES

Update #Staff_Salary_temp Set Basic = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual

Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'BS'

Update #Staff_Salary_temp Set PF = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual

Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'PF'

Update #Staff_Salary_temp Set HRA = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'HRA'

I HAVE CHANGED AS LIKE

Update #Staff_Salary_temp

Set Basic = case ELE_Element_Code When 'BS' then ELE_Actual_rate Else ISNULL(Basic,0.00) end,
PF = case ELE_Element_Code When 'PF' then ELE_Actual_rate Else ISNULL(PF,0.00) end,
HRA = case ELE_Element_Code When 'HRA' then ELE_Actual_rate Else ISNULL(HRA,0.00) end
From #Staff_Salary_temp ,
@ELE_Actual
Where And Employee_Code = ELE_Emp_Code

But The first part is doing correct
the problem is in second part, what's bug..?
-- KK

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-23 : 08:19:48

what error you are getting??

Chirag

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-23 : 08:20:42

it doesnot update.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-23 : 08:26:13

I have this much of updates , This is the need to convert to a single update

Update #Staff_Salary_temp Set Basic = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual

Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'BS'

Update #Staff_Salary_temp Set PF = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual

Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'PF'

Update #Staff_Salary_temp Set HRA = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'HRA'

Update #Staff_Salary_temp Set LWF = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'LWF'

Update #Staff_Salary_temp Set Conveyance = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'CON'

Update #Staff_Salary_temp Set Conveyance = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'CON'

Update #Staff_Salary_temp Set PTAX = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'PTAX'

Update #Staff_Salary_temp Set MPA = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'MPA'

Update #Staff_Salary_temp Set LTAX = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'IT'

Update #Staff_Salary_temp Set Medical = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'MM'

Update #Staff_Salary_temp Set LTA = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'LTA'

Update #Staff_Salary_temp Set Advance = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'ADV'

Update #Staff_Salary_temp Set Arrears = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual
Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code in ('ARINC','AR_ER')

Update #Staff_Salary_temp Set Ltax = ELE_Actual_rate

From #Staff_Salary_temp ,
@ELE_Actual

Where Employee_Code = ELE_Emp_Code
And ELE_Element_Code = 'IT'

Update #Staff_Salary_temp set Organisation_Name = FSC.fs_company_name
FROM COMMON..Fs_Company_master fsc
wHERE fsc.fs_company_code = @C03_Organisation_code

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-24 : 10:00:43

You can do something like this.


Update #Staff_Salary_temp 
Set    Basic = case when ELE_Element_Code = 'BS'  Then ELE_Actual_rate Else Basic End,
       PF    = case when ELE_Element_Code = 'PF'  Then ELE_Actual_rate Else PF    End,
       HRA   = case when ELE_Element_Code = 'HRA' Then ELE_Actual_rate Else HRA   End
From   #Staff_Salary_temp inner join @ELE_Actual
On     Employee_Code = ELE_Emp_Code

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-25 : 06:53:19

thanks khatan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources