Help to understand UPDATE table SET column = value

Screen Link: https://app.dataquest.io/m/192/table-relations-and-normalization/8/challenge-adding-sales-tax-capabilities

This piece of code confusing me:

subtotal = (SELECT total FROM invoice)

My Code:

/home/dq$ sqlite3 chinook.db
-- Loading resources from /home/dq/.sqliterc
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
sqlite> ALTER TABLE invoice
   ...> ADD COLUMN tax NUMERIC;
sqlite> ALTER TABLE invoice
   ...> ADD COLUMN subtotal NUMERIC;
sqlite> UPDATE invoice
   ...> SET 
   ...> tax = 0,
   ...> subtotal = (SELECT total FROM invoice);
sqlite> .headers on
sqlite> SELECT subtotal, total FROM invoice LIMIT 5;


What I expected to happen:

subtotal|total
15.84|15.84
9.9|9.9
1.98|1.98
7.92|7.92
16.83|16.83

What actually happened:

subtotal|total
15.84|15.84
15.84|9.9
15.84|1.98
15.84|7.92
15.84|16.83

To get correct result I should have changed this piece of code:

subtotal = (SELECT total FROM invoice)

to this:

subtotal = total

But dont understand why my original code doesnt work.

1 Like

Doesn’t exactly answer your question but some good lessons: https://codingsight.com/five-ways-update-data-subquery-oracle-sql/

I suspect it’s because the syntax must be SET column = value. In subtotal = (SELECT total FROM invoice), the RHS is a column of values rather than a scalar value, so sqlite automatically selected the 1st value (15.84) from the list to use as value for every row.
Maybe stricter sql flavours will error when you do this.

This is a related thing i tried to learn but can’t find resources: Where to learn what types of SQL constructs used where return what types of data?

2 Likes