UPDATE syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UPDATE syntax

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-28 : 17:36:34

Here are 4 examples of writing an UPDATE .. FROM ..

I was wondering if anyone has any comments on which syntax is best ?
Which is more readable ?, more clear ?
These go against the pubs database and all give the same execution plan.

Also the condition:

s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

You can put in the WHERE section, but you sould also put it in the JOIN ON section.
In my eyes this would go in the WHERE section.

/* 0. This is the BOL example, ANSI JOIN ? */
UPDATE titles
   SET ytd_sales = titles.ytd_sales + sales.qty
      FROM titles, sales
         WHERE titles.title_id = sales.title_id
         AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

/* 1. Not specifying titles in FROM clause */
UPDATE titles SET
	titles.ytd_sales = titles.ytd_sales + s.qty
FROM
	sales s
WHERE
	titles.title_id = s.title_id
	AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

/* 2. Specifying titles in from clause, optionally aliasing it */
UPDATE titles SET
	titles.ytd_sales = t.ytd_sales + s.qty
FROM
	titles t
	JOIN sales s
		ON t.title_id = s.title_id
WHERE
	s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)


/* 3. Specifying alias in the UPDATE clause */
UPDATE t SET
	t.ytd_sales = t.ytd_sales + s.qty
FROM
	titles t
	JOIN sales s
		ON t.title_id = s.title_id
WHERE
	s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-28 : 20:28:13

my favorite is number 3 because it's the most readable. at least to me. imagine if you had ReallyLongTableNamesInYourDatabaseToUse. :)
but it all depends on what kind of update it is.
i do know that number 1 is out my use. used to do it that way, but not anymore.
if there is more than one table i use joins because you know immediatly upon looking at the join how the tables are joined, so i vote that's the most readable.
Where is for filtering data not joinig them IMHO.

All in all i say number 3 is the best. most readable, most clear.

Go with the flow & have fun! Else fight the flow :)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-28 : 20:32:01

Use a join - makes it easier to test as you can just put a select * before the from to see what you will update.
2 and 3 - I always put the table name as part of the upadte clause because that's the first thing you want to know when you look at code.
I don't put the table name to qualify columns being updated - that's defined by the update and the alias just confuses it.
So I go for 2 but without the titles. qualifier.

shame you can't say

update titles t
....
from titles t

maybe

update titles -- t
....
from titles t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 06:31:45

Thank You for your comments.
Up to recently I have been using 1, but I am going through a paradigm shift when it comes to writing updates.
Also it was not until very recently I became aware of 3,

.

In conclusion.
I will go for 2 ( without table alias ) and 3.

/* 2. Specifying titles in from clause, not aliasing it */
UPDATE titles SET
	ytd_sales = titles.ytd_sales + s.qty
FROM
	titles
	JOIN sales s
		ON titles.title_id = s.title_id
WHERE
	s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)


/* 3. Specifying alias in the UPDATE clause */
UPDATE t SET
	ytd_sales = t.ytd_sales + s.qty
FROM
	titles t
	JOIN sales s
		ON t.title_id = s.title_id
WHERE
	s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources