SQL · Theory

Update Join

← All stacks

Theory

66/439

Update Join

If you can teach Update Join using student + marks, you know it. If you only know the heading, you don’t.

For Update Join, InnoDB is in play. Don’t blame it until you have traced student + marks.

Without Update Join, a report gets messy and student + marks is hard to trust.

Zara ships Update Join in a report. That is the use case worth saying.

Update Join miss — missing ON → cartesian product. Fix it before you talk about advanced SQL.

After Update Join, Zara should still remember missing ON → cartesian product.

One breath for Update Join, then student + marks, then missing ON → cartesian product. Sit down.

Diagram

  students.id
       =
  marks.student_id
        │
        ▼
     one result row

Exam tip

For Update Join: definition + a report + one failure.

Update Join — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Mouse', 499);
UPDATE products SET price = 449 WHERE id = 1;
SELECT * FROM products;

Update Join — uPDATE changes existing rows — always scope with WHERE.

Short notes

DefUpdate Join — Zara uses it for student + marks in a report.
RuleUpdate Join → JOIN … ON s.roll = m.roll.
RememberUpdate Join + InnoDB (a report).
UseUpdate Join in a report (student + marks).
TrapUpdate Join — missing ON → cartesian product.
ExUpdate Join → student + marks.

SQL · Theory

Update Join

← All stacks

Theory

66/439

Update Join

If you can teach Update Join using student + marks, you know it. If you only know the heading, you don’t.

For Update Join, InnoDB is in play. Don’t blame it until you have traced student + marks.

Without Update Join, a report gets messy and student + marks is hard to trust.

Zara ships Update Join in a report. That is the use case worth saying.

Update Join miss — missing ON → cartesian product. Fix it before you talk about advanced SQL.

After Update Join, Zara should still remember missing ON → cartesian product.

One breath for Update Join, then student + marks, then missing ON → cartesian product. Sit down.

Diagram

  students.id
       =
  marks.student_id
        │
        ▼
     one result row

Exam tip

For Update Join: definition + a report + one failure.

Update Join — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Mouse', 499);
UPDATE products SET price = 449 WHERE id = 1;
SELECT * FROM products;

Update Join — uPDATE changes existing rows — always scope with WHERE.

Short notes

DefUpdate Join — Zara uses it for student + marks in a report.
RuleUpdate Join → JOIN … ON s.roll = m.roll.
RememberUpdate Join + InnoDB (a report).
UseUpdate Join in a report (student + marks).
TrapUpdate Join — missing ON → cartesian product.
ExUpdate Join → student + marks.

Update Join

Update Join — sample query

Short notes

Questions

Update Join

Update Join — sample query

Short notes

Questions