Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

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