CODEWITHSUNDEEP
sqlsql-serversyntaxsql-update
Ger Ger
Ger Ger

Reputation: 11

Update query statement

I found out something strange which works

Update **X** SET field = 'bla'
from **X**

..which doesn't really make sense

Upvotes: 0

Views: 73

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

The first thing to understand is an UPDATE statement may also use features like JOIN, where more than one table is present. So you can have something like this:

UPDATE X
SET col1 = Y.colA
FROM X
INNER JOIN Y ON X.col2 = y.colB

Now we have two tables involved, and so the X next to UPDATE tells us which table we are targeting.

Of course, we would still prefer X.col1 over just col1 in the SET clause. Make that change, and the X next to the UPDATE keyword seems redundant again. However, it is not legal to target two tables in the same statement. Therefore, the X next to UPDATE tells the server what references are legal targets in the SET clause. It's especially useful when using aliases (which you should pretty much always do):

UPDATE x
SET x.col1 = y.colA, x.col3 = y.colC
FROM VeryLongTableNameA x
INNER JOIN VeryLongTableNameB y ON x.col2 = y.colB

Technically, you could still simplify the language to remove the X next to UPDATE. The query compiler could infer the target based on the SET clause, and multiple or ambiguous targets would fail to compile, producing a syntax error. But that's not how the language works. When you want the full queries to typically complete in single-digit milliseconds, making things even a wee bit easier for the compiler does have value.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311143

This general syntax is usually used for joining tables when updating (i.e., you want to update column c1 in table t1 based on column c2 in table t2). The general for is

UPDATE 
    t1
SET 
    t1.c1 = t2.c2, -- Just an example
    t1.c3 = 'something else' -- etc...
FROM 
    t1
[INNER | LEFT] JOIN t2 ON join_condition

On you example, you don't have a join clause, so it may look a bit funny, but as you noted, this is valid syntax.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

What doesn't make sense?

This is an update statement on X. The X in update x is really the table alias defined by the from clause.

Upvotes: 0

Related Questions