So how does the SQL UPDATE work?

ali-koubeissi

So here I am, sitting at work wondering about everything there is to life. All of the sudden I remembered a question I had when I first was studying database at the university. A question that I'm not being able to answer to this date. The question goes like this: (EMBRACE YOURSELF)

How does an SQL UPDATE query knows when to update a bunch of records with the same value, for example,

 UPDATE Table1 SET Field = value

this will update the table1's filed value to value (all the rows with the same value),
and updating each record with its own modified value, for example,

 UPDATE Table1 SET Field = Field + 'something'

this will update table1's field value according to its current value (each row will have a distinct value).

That question led me to think that I have no idea how an actual update query works. Is just enough for the parser to see that I'm appending to the field and therefore updates each row accordingly?

(END OF EMBRACING YOURSELF)

Does what I just wrote makes any sense whatsoever? WHO AM I?

NoUser

what part confuses you? the update is very simple. it is executed on every row of the table if there is no where clause.
back to programming logic... when you do i=3... your setting i to 3
but when you do i=i+3... your are adding 3 to the existing value of i, and assigning this value to i
the same goes for the update.
it moves row by row updating the value of each field with field +'something'

xterm

Good question, here's a small and rudimentary experiment: (table is rotated -90 degrees for simplicity)

You can test the code here.

class SQL {
def tbl = [
a:[1,4,3],
b:[2,5,7],
c:[3,6,9]
]

def update(Closure c){
tbl.a.eachWithIndex { entry, idx -> tbl.a[idx] = c(entry) }
}
}

sql = new SQL()

// Field = Field + 10
f = { x -> x + 10 }
println sql.update(f)

// Field = value
f = { x -> 10 }
println sql.update(f)

Edit: adjusted comments and naming.

xterm

P.S.: I just noticed that it doesn't replicate 'exactly' the scenario, so forget the block.

MSD

Frankly, I read your question a couple of times trying to get your point but to no avail. So let me try to explain an update as if I am explaining it to someone who know nothing about RDBMSs. This is not necessarily how an update in say SQL Server works but just to explain:

Assume you have a simple text file "Person.txt", the first line holds column names like "ID", "First Name", "Last Name", "Age", etc.
Following lines hold the data for each person where each piece of data falls under its column (in the corresponding order of the column names that is).
Assume we have like a hundred records in the file.
If you were to write some code that would update all first names in the file to "First Name" + "123", how hard would it be?

pseudo code

Read first line to get column names and indexes and append the line to a string ALL
set i to the index of the "First Name" column

while file still has lines
   read a line into string L
   get the value at index i into variable fName
   replace fName with fName + "123" in L
   Append L to the string ALL
End While

Now write L to the original file "Person.txt" overriding the old data

This is BY FAR not a real solution, it is just to explain

saeidw

I understood this as a question about parsing/interpretation.
A parser would see a command like this:

UPDATE Table1 SET Field = Field + 'something'

and break it up into several tokens:

['UPDATE', <update>]
['Table1', <identifier>]
['SET', <update::set>]
['Field', <identifier>]
['=', <EQ>]
['Field', <identifier>]
['+', <PLUS>]
['something', <literal>]

A grammar for SQL would define the UPDATE statement as follows:

  
<update statement: searched>    ::=
         UPDATE <table name> SET <set clause list> [ WHERE <search condition> ]

Having recognized the UPDATE token, the parser can "expect" the structure of the statement. It knows that what follows UPDATE should be a table name. Of course, the parser will also have a rule called <table-name> that defines what table name looks like. It will also know that what follows a SET should be a clause list, and there will be a rule called <clause> that defines what a clause looks like:

<set clause list>    ::=   <set clause> [ { <comma> <set clause> } ... ]
<set clause>    ::=   <object column> <equals operator> <update source>

The parser also knows that the WHERE symbol is optional, but if it sees it, it will expect a <search-condition>.

Now, if you follow that <update-source> tag, you'll see that it explains that "Field + 'something'" is perfectly reasonable.

TL;DR Parsing is fun!

arithma

The right hand side of the update set expression will be evaluated once per field for the 'field = field + 1' and will be calculated once for the whole update for 'field = value'. The compiler could get away with always evaluating all the right hand sides for each row update but it will incur a performance hit.
The code that xterm wrote is essential in understanding this. In one case the closure accesses the row content, and in the other it doesn't depend on the input at all (which it can and should optimize out if the number of rows is large).
Answers your question?

ali-koubeissi

Thank you everyone for the explanation, specially saeidw; the person that actually tried to understand the question. I'm sorry if the question was "meh", it was a "in the moment" question.

xterm

Filtered all unnecessary posts and text. Please stay on topic.

On the other hand, this got me really intrigued to a point where I'm building a simple internal SQL dsl. End result will be something close to this:

sql.with {
  add table "employees" content [['name','salary'],['xterm',500],['ali',1000]]
  update table "employees" set 'name' eq { x -> x + 500 }
  describe 'employees'
}

I'll post the code when I'm done.

xterm

Here we go, an initial pass at an internal SQL dsl

You can run it here.

class SQL {
def tables = [:]

def add(table){
[with: {content -> tables[table] = content}]
}

def describe(table){
println tables[table][0]
}

def update(table){
[set: { map -> map.keySet().each { key ->
def col_idx = tables[table][0].indexOf(key);
tables[table][1..-1].eachWithIndex {
item, idx-> item[col_idx] =
(map."$key" instanceof Closure)?map."$key"(item[col_idx]):map."$key"
}}}]
}

def show(table){
println tables[table]
}
}

users = [
["id", "name", "password", "created"],
[1, "john", "doe", new Date()],
[2, "jane", "smith", new Date()],
[3, "foo", "bar", new Date()]
]

sql = new SQL()

sql.with {
add "users" with users
describe "users"
update "users" set name: "baba"
show "users"
update "users" set id : {x -> x * 10}, name : "xterm"
show "users"
}

xterm

Edit: dropped "eq" and used a HashMap instead that takes multiple column names without the need to specify them as string.

update "users" set id : {x -> x * 10}, name : "xterm"

equals

update("users").set( [ id : {x -> x * 10}, name : "xterm" ] )

Edit #2: Added some visualization(you can't test it in the web console sorry :( )