Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Groups keyboard shortcuts have been updated
Dismiss
See shortcuts

when calling UPDATE from the result set of a SELECT statement, is the order in which rows from the SELECT statement 100% geronteed?

9 views
Skip to first unread message

DR

unread,
Nov 30, 2007, 9:39:05 PM11/30/07
to
when calling UPDATE from the result set of a SELECT statement, is the order
in which rows from the SELECT statement 100% geronteed?

tableA

myid
-----------
0
1
2
3
4
5

UPDATE tableB u
SET myid = i.myid
FROM tableA i
ORDER BY myid

Will this always update tableB with 5 since it is the last one? is this 100%
garonteed to follow the order of the source result set?


--CELKO--

unread,
Nov 30, 2007, 10:58:09 PM11/30/07
to
No. There is no ordering in a table by defintion. Since this strictly
proprietary syntax it an do anything MS feels like next week. You are
just looking for the comfort of a 1960's sequential file system
instead of learning to think in RDBMS.

Tom Cooper

unread,
Nov 30, 2007, 11:11:11 PM11/30/07
to
No. If you want to set a column to the largest value in TableA, one way is

UPDATE tableB
SET myid = (SELECT MAX(i.myid)
FROM tableA i)

Note that that would set the value of myid to 5 in every row of tableB,
which I assume is what you wanted. It was hard to tell exactly what you
wanted to do since the UPDATE query you gave us is not syntacally correct.

Tom

"DR" <softwareen...@yahoo.com> wrote in message
news:OIc2XN8M...@TK2MSFTNGP04.phx.gbl...

Tony Rogerson

unread,
Dec 1, 2007, 6:41:29 AM12/1/07
to
> UPDATE tableB u
> SET myid = i.myid
> FROM tableA i
> ORDER BY myid
>
> Will this always update tableB with 5 since it is the last one? is this
> 100% garonteed to follow the order of the source result set?

Hi DR,

Completely random - depends what is happening at the time in the way the
engine produces the query results.

What you trying to do?

As Tom answers - you can use a sub-query if you are just after the max
value.

Tony

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

DR

unread,
Dec 3, 2007, 2:59:43 PM12/3/07
to
i think your an idiot if you realy want to know what i think lol


"--CELKO--" <jcel...@earthlink.net> wrote in message
news:330376b1-338d-4294...@d61g2000hsa.googlegroups.com...

--CELKO--

unread,
Dec 3, 2007, 3:20:26 PM12/3/07
to
>> I think your an idiot if you really want to know what I think LOL <<

Instead of name calling, why not tell us what your mindset was that
lead you to think that this would ever be a valid statement? My
experience is that this comes from thinking that a table behaves like
a sequential file system, instead an unordered table.

You got this wrong because of some problem with your whole mindset on
RDBMS. I have to write books and teach people SQL and RDBMS, so I
need to know both the right answer (which you got from several other
posters) AND I need to know why the error happened. Mop the floor and
fix the leak!

Perhaps I mistook you for someone who was actually trying to learn
something. If you want to learn, get over it, kid. You might want to
read this:

http://www.apa.org/journals/psp/psp7761121.html

"Unskilled and Unaware of It: How Difficulties in Recognizing One's
Own Incompetence Lead to Inflated Self-Assessments"

0 new messages