Introduction
CTEs with data modifying statements
Summary
14. Summary

Instruction

Perfect! It's time to sum up your knowledge of using CTEs with data modifying statements. In this part, you have learned how to use:

  • CTEs with an INSERT statement:
    WITH AllCustomers AS (
      SELECT
        FirstName,
        LastName,
        Age
      FROM Customer
    )
    
    INSERT INTO Customers
    SELECT
      *
    FROM AllCustomers;
    
  • CTEs with an UPDATE statement:
    WITH Products AS (
      SELECT
        Id
      FROM Product
      WHERE Id IN (1,4,6,7)
    )
    
    UPDATE Product
    SET Price = Price - 0.05
    WHERE Id IN (SELECT Id FROM Products);
    
  • CTEs with a DELETE statement:
    WITH CustomerRemove AS (
      SELECT
        Id
      FROM Customer
      WHERE FirstName = N'Ema' and LastName = N'Green'
    )
    
    DELETE FROM Customer
    WHERE Id = (SELECT Id FROM CustomerRemove);
    

You are also familiar with using nested CTEs with INSERT, UPDATE, or DELETE statements.

Now it's time to try the review exercises. Are you ready?

Exercise

Click Next exercise to continue.