Update problem - 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)

Update problem

Author

Topic

rushdib
Yak Posting Veteran

93 Posts

Posted - 2004-06-04 : 10:30:47

Hi,
I have an update query in which i call a function )returns a table) to get data to update a table. The problem is, it's not updating every single single record. Please tell me what's wrong using this code

UPDATE GLDev SET WRMarCurrYr = GLAmount
FROM dbo.tblGLDevelopment GLDev, dbo.UpdateGLActuals_010_SelectRecords (2004, 3, @localCompanyCode, @localBranchCode, @localDepartmentCode) GL, dbo.tblParmControl_CentreCodePrefix CentreC
WHERE GLDev.BudgetYear = @EnteredYear AND
GLDev.CompanyCode = GL.CompanyCode AND
GLDev.BranchCode = GL.BranchCode AND
GLDev.DepartmentCode = GL.DepartmentCode AND
GLDev.GLCode = GL.GLCode AND
GL.CompanyCode = CentreC.CompanyCode AND
GL.BranchCode = CentreC.BranchCode AND
GL.DepartmentCode = CentreC.DepartmentCode AND
GL.CentreCode = CentreC.CentreCode AND
CentreC.CentreCodePrefix = 'WR'

Thank you for your help,

Rushdi

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-04 : 11:14:30

Can you post the udf?

Might help

And why aren't you using ANSI Join syntax?

My bet is the udf does not return a table....

Brett

8-)

rushdib
Yak Posting Veteran

93 Posts

Posted - 2004-06-04 : 11:40:13

UDF:
CREATE FUNCTION UpdateGLActuals_010_SelectRecords(@EnteredYear smallint,
@EnteredMonth tinyint,
@CompanyCode nvarchar(2),
@BranchCode nvarchar(4),
@DepartmentCode nvarchar(6))
RETURNS TABLE
AS
RETURN
SELECT GL.CompanyCode, GL.BranchCode, DepartmentCode, GL.CentreCode, GLCode,
((GLDebitAmount - GLCreditAmount) / 1000) AS GLAmount
FROM KNAP.dbo.tblGL GL
INNER JOIN Datawarehouse.dbo.tblKNIOrgMstr Org_Mstr
ON GL.CompanyCode = Org_Mstr.CompanyCode AND
GL.BranchCode = Org_Mstr.BranchCode AND
GL.CentreCode = Org_Mstr.CentreCode
WHERE GL.CompanyCode Like replace(@CompanyCode, '*', '%') AND
GL.BranchCode Like replace(@BranchCode, '*', '%') AND
DepartmentCode Like replace(@DepartmentCode, '*', '%') AND
GL.YearGL = @EnteredYear AND
GL.MonthGL = @EnteredMonth

UNION ALL

SELECT GL.CompanyCode, GL.BranchCode, DepartmentCode, GL.CentreCode, GLCode,
((GLDebitAmount - GLCreditAmount) / 1000) AS GLAmount
FROM KNAP.dbo.tblGLArchive GL
INNER JOIN Datawarehouse.dbo.tblKNIOrgMstr Org_Mstr
ON GL.CompanyCode = Org_Mstr.CompanyCode AND
GL.BranchCode = Org_Mstr.BranchCode AND
GL.CentreCode = Org_Mstr.CentreCode
WHERE GL.CompanyCode Like replace(@CompanyCode, '*', '%') AND
GL.BranchCode Like replace(@BranchCode, '*', '%') AND
DepartmentCode Like replace(@DepartmentCode, '*', '%') AND
GL.YearGL = @EnteredYear AND
GL.MonthGL = @EnteredMonth

When I execute the proc, it says it updates 1189 rows. But actually it's not updating all the rows, which have equal joins.

rushdib
Yak Posting Veteran

93 Posts

Posted - 2004-06-04 : 11:58:26

I made a change to the joins like this
UPDATE GLDev SET WRMarCurrYr = GLAmount
FROM dbo.tblGLDevelopment GLDev INNER JOIN dbo.UpdateGLActuals_010_SelectRecords
(2004, 3, '96', '33', 'fw') GL
on GLDev.CompanyCode = GL.CompanyCode AND
GLDev.BranchCode = GL.BranchCode AND
GLDev.DepartmentCode = GL.DepartmentCode AND
GLDev.GLCode = GL.GLCode
INNER JOIN dbo.tblParmControl_CentreCodePrefix CentreC
ON GL.CompanyCode = CentreC.CompanyCode AND
GL.BranchCode = CentreC.BranchCode AND
GL.DepartmentCode = CentreC.DepartmentCode AND
GL.CentreCode = CentreC.CentreCode
WHERE GLDev.BudgetYear = 2004 AND
CentreC.CentreCodePrefix = 'WR'

still it's not updating. When I run the function manually it's bringing the data.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-06-05 : 10:46:05

quote:

And why aren't you using ANSI Join syntax?

using a from clause in update is not ANSI SQL compliant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources