Update From

gvee · #1 (**permalink**) 08-20-08, 08:06

Someone told me recently that UPDATE FROM is proprietary syntax and not the standard.

So what should be used instead?!

I'm mainly a Microsoft SQL Server user so my guess is that, as of version 2005, MERGE is the answer?
What about previous versions?

pootle flump · #2 (**permalink**) 08-20-08, 08:33

I forget the strinct standard syntax but I think it is a series of corrolated subselects. Try googling Celko on this - he is a strong proponent of the standard update syntax.

HTH until someone more standards literate than lil' ol' me comes along.

pootle flump · #3 (**permalink**) 08-20-08, 08:36

Blimey - check out all the luminaries in here for example:
http://www.eggheadcafe.com/software/...-sql-help.aspx

gvee · #4 (**permalink**) 08-21-08, 04:47

Wow, what a complicated premise... Let's see if I understand this:

Code:

USE playdb

CREATE TABLE table_1 (
   some_id     int         PRIMARY KEY
 , some_value  varchar(10)
 , other_value varchar(10)
)

CREATE TABLE table_2 (
   some_id     int         PRIMARY KEY
 , some_value  varchar(10)
 , other_value varchar(10)
)

SET NOCOUNT ON
  INSERT INTO table_1 (some_id, some_value, other_value)
        SELECT 1, NULL, 'a'
  UNION SELECT 2, NULL, 'b'
  UNION SELECT 3, NULL, 'c'

  INSERT INTO table_2 (some_id, some_value, other_value)
        SELECT 1, 'row 1', 'x'
  UNION SELECT 2, 'row 2', 'y'
  UNION SELECT 3, 'row 3', 'z'
SET NOCOUNT OFF

--SELECT * FROM table_1
--SELECT * FROM table_2

BEGIN TRAN --Proprietary UPDATE FROM syntax
  UPDATE table_1
  SET    some_value = t2.some_value
       , other_value = t2.other_value
  FROM   table_1 As [t1]
   INNER
    JOIN table_2 As [t2]
      ON t1.some_id = t2.some_id

  SELECT * FROM table_1
ROLLBACK TRAN

BEGIN TRAN --Still a FROM clause..?
  UPDATE table_1
  SET    some_value  = (SELECT some_value  FROM table_2 t2 WHERE t1.some_id = t2.some_id)
       , other_value = (SELECT other_value FROM table_2 t2 WHERE t1.some_id = t2.some_id)
  FROM   table_1 t1

  SELECT * FROM table_1
ROLLBACK TRAN

GO
DROP TABLE table_1
DROP TABLE table_2

pootle flump · #5 (**permalink**) 08-21-08, 05:32

I can't believe you used proprietary insert syntax too!!!1!

pootle flump · #6 (**permalink**) 08-21-08, 05:33

Ah - I believe this:

Code:

......
BEGIN TRAN --Still a FROM clause..?
  UPDATE table_1
  SET    some_value  = (SELECT some_value  FROM table_2 t2 WHERE t1.some_id = t2.some_id)
       , other_value = (SELECT other_value FROM table_2 t2 WHERE t1.some_id = t2.some_id)
  FROM   table_1 t1

should not include the bit in red to be ANSItastic.

stolze · #7 (**permalink**) 08-21-08, 05:55

Quote:

Originally Posted by georgev

Wow, what a complicated premise... Let's see if I understand this:

Code:

USE playdb

There is no "USE" statement in standard SQL. So this is proprietary already.

Quote:

Code:

SET NOCOUNT ON
  INSERT INTO table_1 (some_id, some_value, other_value)
        SELECT 1, NULL, 'a'
  UNION SELECT 2, NULL, 'b'
  UNION SELECT 3, NULL, 'c'

I have no idea what this SET NOCOUNT ON should do. It isn't standard SQL. Also, statement terminators would be helpful to understand the code better (which is typically a good thing).

The INSERT statement itself doesn't make any sense. You have there "SELECT 1, NULL, 'a'" - select this FROM what? There is the FROM clause missing. You could simplify the multi-row insert to this:

Code:

INSERT INTO table_1 (some_id, some_value, other_value)
VALUES (1, NULL, 'a'),
       (2, NULL, 'b'),
       (3, NULL, 'c')

Quote:

Code:

  UPDATE table_1
  SET    some_value = t2.some_value
       , other_value = t2.other_value
  FROM   table_1 As [t1]
   INNER
    JOIN table_2 As [t2]
      ON t1.some_id = t2.some_id

First, I would remove the brackets around the tables' correlation names because they don't belong there.

Now let's try to read the statement (which is one of the things you can typically do with SQL):
Update "table_1" and set the value in columns "some_value" and "other_value".

Here we start stumbling: Now comes a "FROM" - it simply doesn't fit in the sentence, wouldn't you agree? Even more strange from a logical point of view is that "table_1" occurs in this FROM clause again.

What is more natural and straight-forward would be the following. You define which table shall be updated, calculate the new values and finally you restrict the rows on which the update shall take place.

Code:

UPDATE table_1 AS t1
SET    ( some_value, other_value ) = ( SELECT t2.some_value, other_value
                                       FROM table_2 AS t2
                                       WHERE t1.some_id = t2.some_id )
WHERE EXISTS ( SELECT 1
               FROM table_2 AS t2
               WHERE t1.some_id = t2.some_id )

Some other extensions (which may find their way into the standard) is this:

Code:

UPDATE ( SELECT t1.some_value, t1.other_value, t2.some_value AS new_some_value, t2.other_value AS new_other_value
         FROM table_1 AS t1 JOIN table_2 AS t2 ON WHERE t1.some_id = t2.some_id )
SET some_value = new_some_value, other_value = new_other_value

The DBMS builds a temporary table that does the join, then applies the update on this temporary table and maps it back to the underlying base tables or views. Of course, this will only work if such a mapping-back can be done in the first place and the columns being updated are updateable.

gvee · #8 (**permalink**) 08-21-08, 06:27

Happier syntax?

Code:

CREATE TABLE table_1 (
   some_id     int         PRIMARY KEY
 , some_value  varchar(10)
 , other_value varchar(10)
);

CREATE TABLE table_2 (
   some_id     int         PRIMARY KEY
 , some_value  varchar(10)
 , other_value varchar(10)
);

INSERT INTO table_1 (some_id, some_value, other_value)
VALUES (1, NULL, 'a');
INSERT INTO table_1 (some_id, some_value, other_value)
VALUES (2, NULL, 'b');
INSERT INTO table_1 (some_id, some_value, other_value)
VALUES (3, NULL, 'c');

INSERT INTO table_2 (some_id, some_value, other_value)
VALUES (1, 'row 1', 'x');
INSERT INTO table_2 (some_id, some_value, other_value)
VALUES (2, 'row 2', 'y');
INSERT INTO table_2 (some_id, some_value, other_value)
VALUES (3, 'row 3', 'z');

SELECT * FROM table_1;
SELECT * FROM table_2;

GO
DROP TABLE table_1;
DROP TABLE table_2;

I'm still struggling to get my head round the update logic - but this is probably down to the FROm method being embedded in my memory!

I'll give this a more thorough read later today, but I have a couple of minor questions that are somewhat unrelated from the UPDATE syntax.

Quote:

Originally Posted by stolze

I would remove the brackets around the tables' correlation names because they don't belong there.

Why should they be removed, I appreciate that they are not needed in this instance, is that the only reason or am I missing something?

Now this may be a large leap in logic, but I predict that the standard method over large resultsets, especially when updating more than one column at a time, could be less efficient than the prorietary method - is this assumption near true?

pootle flump · #9 (**permalink**) 08-21-08, 08:00

Stolze - I'm like George and predominantly use MS T-SQL and know little of the standard. Is this standard SQL?

Code:

UPDATE table_1 AS t1
SET    ( some_value, other_value ) = ( SELECT t2.some_value, other_value
                                       FROM table_2 AS t2
                                       WHERE t1.some_id = t2.some_id )

???
If so then the infamous UPDATE....FROM would be totally unnecessary if T-SQL supported the standard fully.

In case you are interested (bet you ain't) SET NOCOUNT ON just tells SQL Server not to count, and return, the number of rows affected by the proceeding statements.

stolze · #10 (**permalink**) 08-21-08, 08:59

Quote:

Originally Posted by pootle flump

Stolze - I'm like George and predominantly use MS T-SQL and know little of the standard. Is this standard SQL?

Code:

UPDATE table_1 AS t1
SET    ( some_value, other_value ) = ( SELECT t2.some_value, other_value
                                       FROM table_2 AS t2
                                       WHERE t1.some_id = t2.some_id )

???

Yes, this is standard SQL.

Quote:

If so then the infamous UPDATE....FROM would be totally unnecessary if T-SQL supported the standard fully.

Updating multiple values as shown above is a bit independent from what UPDATE ... FROM tries to achieve. UPDATE ... FROM tries to avoid the necessity to specify the same subselect twice: once in the SET clause and once in the WHERE clause of an update statement. However, (and that answers georgev's question, too) any decent system detects that the same subselect has been used there and only executes it once. So the argument of being less efficient doesn't hold any water.

stolze · #11 (**permalink**) 08-21-08, 09:14

Quote:

Originally Posted by georgev

Happier syntax?

[CODE]
INSERT INTO table_1 (some_id, some_value, other_value)
VALUES (1, NULL, 'a');
INSERT INTO table_1 (some_id, some_value, other_value)
VALUES (2, NULL, 'b');
INSERT INTO table_1 (some_id, some_value, other_value)
VALUES (3, NULL, 'c');

Yes, but too excessive executing 3 statements instead of just 1 ;-)

And the "GO" is also not standard SQL ;-)

Quote:

Why should they be removed, I appreciate that they are not needed in this instance, is that the only reason or am I missing something?

It's not standard SQL. A <correlation name> in a <from clause> is an <identifier>. What identifiers are is defined in subclause 5.4, "Names and identifiers" in the SQL standard (I'm looking at SQL:2003). There, an identifier is either a <regular identifier>, a <delimited identifier>, or a <unicode delimited identifier>. All delimited identifiers have to have double-quotes around them and unicode delimited identifiers have a leading 'U'. Only with delimited identifiers you could have a character like '[' in the identifier name. Since you don't have double-quotes there, it must be a regular identifier, in which case the first character must be one of (cf. Note 58 in SQL:2003 in subclause 5.2):

Quote:

upper-case letters, lower-case letters, title-case letters, modifier letters, other letters, and letter numbers

Only letters and numbers are mentioned there. Thus, the brackets are not part of the identifier (and you didn't use it as such). But the standard syntax says:

Quote:

<table or query name> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]

The ']' indicate optional arguments in the BNF notation and not characters that could by typed in. Thus, my take on this is that using "table_1 AS [t1]" is a proprietary product extension of T-SQL only.

r937 · #12 (**permalink**) 08-21-08, 09:48

Quote:

Originally Posted by stolze

Thus, my take on this is that using "table_1 AS [t1]" is a proprietary product extension of T-SQL only.

ding ding ding ding ding

we have a winnah!!!

dportas · #13 (**permalink**) 08-28-08, 15:26

Quote:

Originally Posted by georgev

I'm mainly a Microsoft SQL Server user so my guess is that, as of version 2005, MERGE is the answer?
What about previous versions?

MERGE is the answer as from SQL Server 2008. It ought to make the atrocious and flawed UPDATE ... FROM obsolete. True, MERGE has a lousy syntax but it avoids the problems of Microsoft's UPDATE and in many cases MERGE is more efficient than the equivalent UPDATE ... FROM.

stolze · #14 (**permalink**) 09-01-08, 05:09

MERGE is standard SQL, too. ;-)

Internet Services

Web Development

Digital Marketing

Consumer Tech