February 19, 2008 at 6:03 pm
This is one of those “I know I’m looking right at it” situations.
The following works except that it updates all of the employees in the table, not just the one desired.
BEGIN
Select Employee_PK_ID
FROM
tbl_EmployeeBasicInformation
WHERE
@EmployeeNumber =
tbl_EmployeeBasicInformation.EmployeeNumber
UPDATE tbl_EmployeeWageRates
SET
AverageWage = coalesce(@AverageWage, AverageWage),
FringeWage = coalesce(@FringeWage, FringeWage),
SpecialWage = coalesce(@SpecialWage, SpecialWage),
GuaranteedWage = coalesce(@GuaranteedWage, GuaranteedWage),
FutureUseField1 = coalesce(@FutureUseField1, FutureUseField1),
WageChangeDate = coalesce(@WageChangeDate, WageChangeDate)
WHERE
Employee_PK_ID =
tbl_EmployeeWageRates.Employee_PK_ID
END
Thanks again.
I'll get there eventually.
Bill
February 19, 2008 at 6:51 pm
Try assigning the value of Employee_PK_ID to a variable in your first select statement.
Then in the where clause of the update statement, have:
[font="Courier New"]WHERE @Employee_PK_ID_variable = tbl_EmployeeWageRates.Employee_PK_ID[/font]
As it is currently written, there is no link between the two statements.
Alternatively, you could encapsulate the first select statement into the WHERE clause of the update statement (which would be the better of the two options):
[font="Courier New"]
WHERE Employee_PK_ID = (Select Employee_PK_ID
FROM tbl_EmployeeBasicInformation
WHERE
@EmployeeNumber = tbl_EmployeeBasicInformation.EmployeeNumber)
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 19, 2008 at 8:08 pm
Scott, thanks. With your help, I got it to work.
Bill
BEGIN
UPDATE tbl_EmployeeWageRates
SET
AverageWage = coalesce(@AverageWage, AverageWage),
FringeWage = coalesce(@FringeWage, FringeWage),
SpecialWage = coalesce(@SpecialWage, SpecialWage),
GuaranteedWage = coalesce(@GuaranteedWage, GuaranteedWage),
FutureUseField1 = coalesce(@FutureUseField1, FutureUseField1),
WageChangeDate = coalesce(@WageChangeDate, WageChangeDate)
WHERE
Employee_PK_ID =
(
Select Employee_PK_ID
FROM
tbl_EmployeeBasicInformation
WHERE
@EmployeeNumber =
tbl_EmployeeBasicInformation. EmployeeNumber)
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply