SQL How UPDATE command works

Question

How does the UPDATE command in SQL work?
I have a table with three columns a,b,c where a is the primary key.
the values in the table are (1,2,3),(2,4,5).
Suppose i want to update this tuple and set the value of column c to 4 where values of a is 1.
Is the tuple (1,2,3) deleted and then a new tuple is inserted ?
Or the values 1,2 are kept constant 3 is deleted and 4 is inserted ?

Answer 1

Since you didn't specify which databases are of particular interest, I'm going to pick MySQL. (But it's not too far-fetched to say most other relational databases have similar implementation.)

In a nutshell, MySQL UPDATE supports both single-table syntax and multiple-table syntax. In both cases, the tuples are not deleted, then re-inserted. The edits happened directly to the columns specify by the SET clause.

Right at the top of the docs, it says that

For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values.

then

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the conditions multiple times.

There is no mentions of a DELETE THEN RE-INSERT operation. In your case, yes, values 1 and 2 are kept constant, while 3 is replaced by 4.