August 2, 2020 at 10:58 am
In the query below do I need to join tables Brs to MYCTE in the UPDATE portion of the query?
Is there a shorter way to write this update query?
Cheers,
J.
DECLARE @IDHUIS VARCHAR(4) = NULL, @IDBEW INT = NULL
--SET @IDHUIS = 'PH'
--SET @IDBEW = 1401
;WITH MYCTE AS
(
SELECTB.IDHuis,
B.zzpclient_id,
Sum( A.FreqGetal * MA.Norm / 60 ) AS [HrsWerk]
FROM MasterActiviteitenLijst AS MA
INNER JOIN Activiteiten AS A
ON MA.IDHuisIDAct = A.IDHuisIDAct
INNER JOIN Bewoners AS B
ON B.IDHuisIDBew = A.IDHuisIDBew
WHEREMA.GroepCode = 'HKB'
AND (B.zzpclient_id = @IDBEW OR @IDBEW IS NULL)
AND (B.IDHuis = @IDHUIS OR @IDHUIS IS NULL)
GROUP BY B.zzpclient_id, B.IDHuis
)
UPDATEBrs
SETHrsHKB = M.HRSWERK
FROMMYCTE M
WHEREM.IDHuis = Brs.IDHuis AND
M.zzpclient_id = Brs.zzpclient_id
August 2, 2020 at 11:45 pm
The problem here is that you are trying to update a table you aren't even referencing in the UPDATE statement's FROM clause. I'm not really sure why you're asking the question, though, as I don't see any indication of what problem you are having. Are you getting a syntax error? Something else? I can only guess that your failure to join to the table being updated is at least a part of your problem. You might also want to include a description of the objective of this update, so that we have some idea of your overall objective.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 3, 2020 at 3:14 pm
I don't see a more efficient way. The speed problem may be that it ise UPDATEing every row of the Brs table, since there's no join to it in the query.
Maybe try this instead:
UPDATEB
SETHrsHKB = M.HRSWERK
FROM dbo.Brs B
INNER JOIN MYCTE M
ONM.IDHuis = B.IDHuis AND
M.zzpclient_id = B.zzpclient_id
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".
August 5, 2020 at 2:30 pm
Scott and Steve: thanks, there should have been a join, not sure why it was missed.
Cheers,
J.
August 5, 2020 at 5:03 pm
Great, glad it helped.
Btw, note that you must ALWAYS use the alias in the UPDATE statement when using a join in an UPDATE.
--WRONG!!
UPDATE Brs --<<--WRONG!! MUST be (alias) B
SET HrsHKB = M.HRSWERK
FROM dbo.Brs B --<<-- alias assigned to table, as it should be
INNER JOIN MYCTE M
ON M.IDHuis = B.IDHuis AND
M.zzpclient_id = B.zzpclient_id
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".
August 5, 2020 at 5:31 pm
Yes, thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply