BBampaa

Lessons › SQL › Deep Dive: Database Admin & ETL

SQL lesson

SCD Type 1: overwriting a change

SCD Type 1 simply overwrites a customer's old value with the new one. Only today's value is kept.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

When the old value doesn't matter: overwrite it

UPDATE customers SET email = 'ada.obi@mail.com' WHERE customer_id = 'C101';
SELECT * FROM customers

Result:

customer_idfull_nameregionemail
C102Ben ColeWestben@mail.com
C103Cara DiazEastcara@mail.com
C101Ada ObiEastada.obi@mail.com

Step by step

  1. SET gives the new value. The old one is overwritten, gone for good.
    UPDATE customers SET email = 'ada.obi@mail.com'
  2. WHERE makes sure only Ada changes. Without it, every customer would get this email.
    UPDATE customers SET email = 'ada.obi@mail.com' WHERE customer_id = 'C101';
  3. One row per customer, with today's value.
    UPDATE customers SET email = 'ada.obi@mail.com' WHERE customer_id = 'C101';
SELECT * FROM customers;

Practice

Try each one in your head first, then open the answer.

1. Type 1: change C102's email to 'ben.cole@mail.com', then show all customers.
UPDATE customers SET email = 'ben.cole@mail.com' WHERE customer_id = 'C102'; SELECT * FROM customers
2. Type 1: change C103's region to 'North', then show all customers.
UPDATE customers SET region = 'North' WHERE customer_id = 'C103'; SELECT * FROM customers
3. Type 1: correct C101's full_name to 'Ada Obi-Okafor', then show customer_id and full_name.
UPDATE customers SET full_name = 'Ada Obi-Okafor' WHERE customer_id = 'C101'; SELECT customer_id, full_name FROM customers
4. Type 1: change C102's region to 'North' and email to 'ben@north.com' in one UPDATE, then show all customers.
UPDATE customers SET region = 'North', email = 'ben@north.com' WHERE customer_id = 'C102'; SELECT * FROM customers
5. Type 1: every customer in 'East' is now in 'Eastern'. Update them, then show customer_id and region.
UPDATE customers SET region = 'Eastern' WHERE region = 'East'; SELECT customer_id, region FROM customers

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed