Hi, i'm looking for a deeper understanding of the relationship between SELECT and UPDATE. I would like to be able to speak to why there are similarities and why there are difference based on the clauses each offer. Thanks in advance for any help!
let's use a shopping analogy for fruits
--you were at the store and picked up apples.
select * from store where fruit = 'Apple'
--you went to the store and picked up 5 apples
select * from store where fruit = 'Apple' and quantity = 5
--you went to the store and picked up 5 red apples
select * from store where fruit = 'Apple' and quantity = 5 and color = 'red'
--you went to the store and picked up 5 red apples
--put them in shopping bag
--and when you got home you sliced one apple into 4 slices
update tgt
set tgt.action = 'Slice into 4 slices'
from shoppingbag tgt
where fruit = 'Apple'
and quantity = 1
and color = 'red'
SELECT does not make any changes to the underlying table you are querying whereas UPDATE could
SELECT retrieves data from (one or more) table(s) or views without making ANY changes to the data.
UPDATE changes the data in a single table or updateable view
In this sense, SELECT can be considered as "Data Query Language" construct and UPDATE as "Data Modification Language" construct, or DQL vs DML. See here
A Select query merely returns the records you specify. An SQLUpdate query, on the other hand, actually makes some change to those records. You might run this query to capitalize all State names in a table for instance.