Skip to content
Advertisement

Update statement SQL with CTE

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 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

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:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement