Spring Deals - hours only!Up to 70% off on all courses and bundles.-Close
Introduction
INSERT with SELECT
Referring to other tables
INSERT, UPDATE and DELETE with JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary
15. Summary

Instruction

Amazing! Let's summarize what we have learned. You know how to:

  • Use INSERT INTO with SELECT to insert data from one table into another:
  • INSERT INTO HistoryPurchase 
    SELECT * FROM Purchase;
  • Delete information from one table based on data from another table, as in DELETE FROM ... FROM:
  • DELETE FROM Purchase 
    FROM Product 
    WHERE Product.Id = Purchase.ProductId 
    AND Product.Price IS NULL;
  • Update data in one table based on data from another table, using UPDATE ... FROM:
  • UPDATE Purchase 
    SET Purchase. TotalPrice = Product.Price * Purchase.Quantity 
    FROM Product 
    WHERE Purchase.ProductId = Product.Id;
  • Use JOIN in INSERT, UPDATE, and DELETE statements:
  • DELETE Purchase 
    FROM Purchase 
    JOIN Product ON Purchase.ProductId = Product.Id 
    WHERE Product.DeliveredDateTime < 2017;
  • Write INSERT, UPDATE, and DELETE statements that include subqueries:
  • DELETE FROM Product 
    WHERE Id NOT IN (SELECT ProductId FROM Order) 
    AND YEAR(DeliveredDateTime) < YEAR(GETDATE())- 3;

Note: INSERT, UPDATE, and DELETE operations can also include common table expressions (CTEs). However, an explanation of that is beyond the scope of this course. If you would like to learn more about it, check out the Recursive Queries in SQL Server course that we offer.

Exercise

Congratulations! That's all we wanted to teach you about How to insert, update, or delete data in SQL Server. In the next part, you'll test your knowledge with some cumulative practice exercises.

Click Next exercise to proceed to the final quiz.