December 13, 2017 at 2:22 pm
The following code gives me the correct value for HV2Norm.
SELECT IDBew, ZPTDoelgroep, ZPTIND, HV2Norm
FROM
(
SELECT B.IDBew, B.ZPTDoelgroep, B.ZPTIND, M.HV2Norm
FROM MasterTabelHV2NormNew M JOIN Bewoners B
ON M.ZZPID_DG = B.ZPTind AND
M.Jaar = DATEPART(YEAR, B.DateInsert)
WHERE B.ZPTInd IN ('1VV','2VV','3VV')
UNION ALL
SELECT B.IDBew, B.ZPTDoelgroep, B.ZPTIND, M.HV2Norm
FROM MasterTabelHV2NormNew M JOIN Bewoners B
ON M.ZZPID_DG = B.ZPTDoelgroep AND
M.Jaar = DATEPART(YEAR, B.DateInsert)
WHERE B.ZPTInd NOT IN ('1VV','2VV','3VV')
) foo
ORDER BY foo.ZPTInd
However this UPDATE code below does not.
Every row gets the same value for HV2Norm.
What am I missing?
Cheers,
Julian
-- USE indicatie if ZZP INDICATIE IS 1VV or 2VV or 3VV
UPDATE Bewoners_STAGING
SET Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
FROM MasterTabelHV2NormNew M JOIN Bewoners B
ON M.ZZPID_DG = B.ZPTind AND
M.Jaar = DATEPART(YEAR, B.DateInsert)
WHERE B.ZPTInd IN ('1VV','2VV','3VV');
-- USE doelgroep IF ZZP INDICATIE IS NOT 1VV or 2VV or 3VV
UPDATE Bewoners_STAGING
SET Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
FROM MasterTabelHV2NormNew M JOIN Bewoners B
ON M.ZZPID_DG = B.ZPTDoelgroep AND -- <-- use DOELGROEP
M.Jaar = DATEPART(YEAR, B.DateInsert)
WHERE B.ZPTInd NOT IN ('1VV','2VV','3VV');
December 13, 2017 at 2:43 pm
Is there a 1 to 1 match between both tables? Check the row count you get from the select vs how many records get updated.
December 13, 2017 at 3:21 pm
You haven't defined the relationship between the table that you are updating and the source data, so it's as if you're updating every single row in the table with every single value from the results, but SQL is smart enough to only update it with the "last" value. This is why certain people on this forum dislike this variant of the UPDATE statement.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 13, 2017 at 3:29 pm
Ooops, how obvious.
Should be the staging table.
December 14, 2017 at 4:06 am
drew.allen - Wednesday, December 13, 2017 3:21 PMYou haven't defined the relationship between the table that you are updating and the source data, so it's as if you're updating every single row in the table with every single value from the results, but SQL is smart enough to only update it with the "last" value. This is why certain people on this forum dislike this variant of the UPDATE statement.Drew
What would be an alternative to this UPDATE statement?
UPDATE Bewoners_STAGING
SET Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
FROM MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
ON M.ZZPID_DG = B.ZPTind AND
M.Jaar = DATEPART(YEAR, B.DateEdit)
WHERE B.ZPTInd IN ('1VV','2VV','3VV');
December 14, 2017 at 8:33 am
JJR333 - Thursday, December 14, 2017 4:06 AMdrew.allen - Wednesday, December 13, 2017 3:21 PMYou haven't defined the relationship between the table that you are updating and the source data, so it's as if you're updating every single row in the table with every single value from the results, but SQL is smart enough to only update it with the "last" value. This is why certain people on this forum dislike this variant of the UPDATE statement.Drew
What would be an alternative to this UPDATE statement?
UPDATE Bewoners_STAGING
SET Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
FROM MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
ON M.ZZPID_DG = B.ZPTind AND
M.Jaar = DATEPART(YEAR, B.DateEdit)
WHERE B.ZPTInd IN ('1VV','2VV','3VV');
If you look at the likes of Oracle, the UPDATEs there have no FROM clause. Instead, you have to write correlated subqueries in the WHERE clause and it makes things very slow in SQL Server. Another possibility is to use MERGE to do you updates but that has a raft of it's own problems.
Shifting gears a bit, you should get into the habit of using the 2 part naming convention for both performance and data-safety reasons (could have multiple identically named tables in different schemas).
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2017 at 10:20 am
When joining to do UPDATEs, always update the table alias not the table name itself. Otherwise you could have the same problem (or other problems).
UPDATE B
SET B.ZPTHV2NormHrs = M.HV2Norm
FROM MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
ON M.ZZPID_DG = B.ZPTind AND
M.Jaar = DATEPART(YEAR, B.DateInsert)
WHERE B.ZPTInd IN ('1VV','2VV','3VV');
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 14, 2017 at 11:46 am
JJR333 - Thursday, December 14, 2017 4:06 AMdrew.allen - Wednesday, December 13, 2017 3:21 PMYou haven't defined the relationship between the table that you are updating and the source data, so it's as if you're updating every single row in the table with every single value from the results, but SQL is smart enough to only update it with the "last" value. This is why certain people on this forum dislike this variant of the UPDATE statement.Drew
What would be an alternative to this UPDATE statement?
UPDATE Bewoners_STAGING
SET Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
FROM MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
ON M.ZZPID_DG = B.ZPTind AND
M.Jaar = DATEPART(YEAR, B.DateEdit)
WHERE B.ZPTInd IN ('1VV','2VV','3VV');
I've taken to liking to use CTE to handle joined updates, the update statement itself becomes very straight forward and it's very easy to verify the result set before actually running the update. So you might have something like this, just switch the comments around if the results look good.
WITH TEMP_CTE AS ( SELECT B.ZPTHV2NormHrs, M.HV2Norm, M.ZZPID_DG, B.ZPTind, M.Jaar, B.DateEdit FROM MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
ON M.ZZPID_DG = B.ZPTind AND
M.Jaar = DATEPART(YEAR, B.DateEdit)
WHERE B.ZPTInd IN ('1VV','2VV','3VV');
)
SELECT * FROM TEMP_CTE
--UPDATE TEMP_CTE SET ZPTHV2NormHrs = HV2Norm
December 15, 2017 at 2:00 pm
My approach using the comments above:
With myCTE AS
(
B.ZPTIDHuis AS IDHUIS, B.ZPTIDAfd AS IDAFD
FROM Bewoners_STAGING B
WHERE B.ZPTCalc <> 'n' AND B.AfdMeetellen <> 'nee'
)
UPDATE B
SET B.AantalCltn = C.CltAfd
FROM Bewoners_STAGING B JOIN myCTE C
ON B.ZPTIDHuis = C.IDHuis AND B.ZPTIDAfd = C.IDAfd
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply