I have a common table expression that I am using trying to use as an update statement. The only reason for the CTE is so I can use a where clause to filter by CredCount. I would like to use that where clause to update only records that match in this case CredCount of 2. However, I am having trouble with the update part of the query.
Members_id | Credentials | CredCount | members_amountdue |
---|---|---|---|
1 | CMA, CPR | 2 | 0 |
2 | CMA, CPR | 2 | 0 |
3 | CMA, CPR | 2 | 0 |
Here is the query for that
WITH CTE AS ( SELECT members_id, members_amountdue, [Credentials], LEN([Credentials]) - LEN(REPLACE([Credentials], ',', '')) + 1 AS CredCount FROM ( SELECT DISTINCT mem1.members_id, mem1.members_amountdue, STUFF( (SELECT DISTINCT ', ' + credentials_code FROM members AS mem JOIN members_credentials AS mc ON mc.members_id = mem.members_id JOIN credentials AS c ON c.credentials_id = mc.credentials_id WHERE mem.[members_id] = mem1.[members_id] FOR XML PATH ('')), 1, 1, '') AS [Credentials] FROM members AS mem1 JOIN members_status as ms on ms.members_status_id = mem1.members_status_id ) AS derived), CTE2 AS ( SELECT members_id FROM members) SELECT CTE.members_id, CTE.Credentials, CTE.CredCount, (CTE.members_amountdue + 25) as NewPriceTotal FROM CTE JOIN CTE2 ON CTE.members_id = CTE2.members_id WHERE CTE.CredCount = 2
With the update statement I was looking at an example provided here at Update records in table from CTE so I added the update statement to the bottom of the query
WITH CTE AS ( SELECT members_id, members_amountdue, [Credentials], LEN([Credentials]) - LEN(REPLACE([Credentials], ',', '')) + 1 AS CredCount FROM ( SELECT DISTINCT mem1.members_id, mem1.members_amountdue, STUFF( (SELECT DISTINCT ', ' + credentials_code FROM members AS mem JOIN members_credentials AS mc ON mc.members_id = mem.members_id JOIN credentials AS c ON c.credentials_id = mc.credentials_id WHERE mem.[members_id] = mem1.[members_id] FOR XML PATH ('')), 1, 1, '') AS [Credentials] FROM members AS mem1 JOIN members_status as ms on ms.members_status_id = mem1.members_status_id ) AS derived), CTE2 AS ( SELECT members_id FROM members) SELECT CTE.members_id, CTE.Credentials, CTE.CredCount, (CTE.members_amountdue + 25) as NewPriceTotal FROM CTE JOIN CTE2 ON CTE.members_id = CTE2.members_id WHERE CTE.CredCount = 2 UPDATE members set members_amountdue = NewPriceTotal
When I add the update statement I get an error for invalid column name for ‘NewPriceTotal’. I know I need the column to match in order for the update to work but I’m not sure why it’s invalid.
Members_id | Credentials | CredCount | NewPriceTotal |
---|---|---|---|
1 | CMA, CPR | 2 | 25 |
2 | CMA, CPR | 2 | 25 |
3 | CMA, CPR | 2 | 25 |
I would like for the NewPriceTotal to be set at the members_amountdue from the members table but I’m not sure where I made the wrong turn at. Any help, comments or feedback is greatly appreciated.
Advertisement
Answer
Try this:
.... CTE2 AS ( SELECT members_id FROM members ), CTE3 AS ( SELECT CTE.members_id, CTE.Credentials, CTE.CredCount, CTE.members_amountdue, (CTE.members_amountdue + 25) as NewPriceTotal FROM CTE JOIN CTE2 ON CTE.members_id = CTE2.members_id WHERE CTE.CredCount = 2) UPDATE CTE3 SET members_amountdue = NewPriceTotal