UPDATE statement on CTE with INNER JOIN

Question

UPDATE statement on CTE with INNER JOIN

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 1

CTE is like a derived table, so it could, or it could just be a collection of values, like "SELECT 1 as someNum UNION SELECT 2 UNION SELECT 3"

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

michael.leach2015 wrote:

Lynn Pettis wrote:
Jeff Moden wrote:
Lynn Pettis wrote:
May I ask why you are using CTEs in this manner when you could just use the tables themselves?
That was going to be my exact question.... or is this just an experiment to see how CTEs work in an UPDATE.
And we did not even get an answer to the question.
Since I am new to CTEs (and SQL) I just wanted to do a CTE to see how it works. Because of this, it looks like I inadvertantly made a lot of mistakes. So this was just an experiment to see if I can create a CTE and get an idea of how they work. Therefore my example doesn't have any practical use.
Lynn,
I thought that a CTE definitions would always refer to some underlying table.

Knowing the intent of the question helps in understanding what you were attempting. The example you provided was contrived and it does provide a means to an end. Here is the problem I have with it. You added complexity to a problem that didn't require it. It is like finding dynamic SQL in a procedure and when you finish analyzing it, there was no need for the dynamic SQL to begin with. And yes, that is exactly an issue I just cleaned up in a stored procedure being used in a production environment.

There are good reasons for learning and using CTEs. I would recommend doing more research and looking for problems here on ssc that have been solved well using CTEs.

One use is to delete duplicate data from a table. Properly written the CTE acts like an updateable view to the underlying table and allows the duplicate values to be identified and deleted.

Another is building a hierarchy which would use a recursive CTE.

The biggest thing about learning how to write and use CTEs is also learning when not to use them.

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 3

Lynn Pettis wrote:

michael.leach2015 wrote:
Lynn Pettis wrote:
Jeff Moden wrote:
Lynn Pettis wrote:
May I ask why you are using CTEs in this manner when you could just use the tables themselves?
That was going to be my exact question.... or is this just an experiment to see how CTEs work in an UPDATE.
And we did not even get an answer to the question.
Since I am new to CTEs (and SQL) I just wanted to do a CTE to see how it works. Because of this, it looks like I inadvertantly made a lot of mistakes. So this was just an experiment to see if I can create a CTE and get an idea of how they work. Therefore my example doesn't have any practical use.
Lynn,
I thought that a CTE definitions would always refer to some underlying table.
Knowing the intent of the question helps in understanding what you were attempting.

I will try to keep this in mind going forward. I didn't mean to cause any confusion to anybody. My apologies.