DML - Managing Retail Data

Efficiency is critical in high-volume retail. When seeding a new store with 10,000 products, sending 10,000 INSERT commands would be slow and resource-heavy. SQL allows us to insert multiple rows in a single statement, drastically reducing network overhead.

Furthermore, the ON CONFLICT (Upsert) clause allows us to decide what happens if a record already exists. Instead of an error, we can choose to update the existing record, ensuring our inventory levels stay accurate without duplicates.

INSERT INTO staff (id, name, role)VALUES (101, 'Alice', 'Manager'), (102, 'Bob', 'Clerk')ON CONFLICT (id) DO NOTHING;

Often, the data you want to change depends on information in another table. For example, you might want to increase the price of all items supplied by "GlobalVogue Corp".

In PostgreSQL, you can use the FROM clause in an UPDATE statement to "Join" tables. This allows for highly targeted mass-updates based on complex business rules across your entire schema.

UPDATE pricesSET amount = amount * 1.05FROM suppliersWHERE prices.supplier_id = suppliers.idAND suppliers.rating > 4.5;

The DELETE command must be used with extreme caution. In a retail database, we rarely delete "everything". Instead, we use precise WHERE clauses to remove temporary session data, cancelled orders, or expired promotional codes.

Pro Tip: Always run a SELECT with the same WHERE clause before running a DELETE to verify exactly which rows will be removed!

DELETE FROM promo_codesWHERE expiry_date < '2025-01-01'AND usage_count = 0;