Gmail Calendar Documents Reader Web more »
Recently Visited Groups | Help | Sign in
Google Groups Home
microsoft.public.sqlserver.programming
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
when calling UPDATE from the result set of a SELECT statement, is the order in which rows from the SELECT statement 100% geronteed?
Options
There are currently too many topics in this group that display first. To make this topic appear first, remove this option from another topic.
There was an error processing your request. Please try again.
flag
   6 messages - Collapse all  -  Translate all to Translated (View all originals)
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
DR  
View profile  
 More options Nov 30 2007, 9:39 pm
Newsgroups: microsoft.public.sqlserver.programming
From: "DR" <softwareengineer98...@yahoo.com>
Date: Fri, 30 Nov 2007 18:39:05 -0800
Local: Fri, Nov 30 2007 9:39 pm
Subject: when calling UPDATE from the result set of a SELECT statement, is the order in which rows from the SELECT statement 100% geronteed?
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author
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?


    Reply to author    Forward  
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
--CELKO--  
View profile  
(1 user)  More options Nov 30 2007, 10:58 pm
Newsgroups: microsoft.public.sqlserver.programming
From: --CELKO-- <jcelko...@earthlink.net>
Date: Fri, 30 Nov 2007 19:58:09 -0800 (PST)
Local: Fri, Nov 30 2007 10:58 pm
Subject: Re: when calling UPDATE from the result set of a SELECT statement, is the order in which rows from the SELECT statement 100% geronteed?
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author
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.

    Reply to author    Forward  
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Tom Cooper  
View profile  
 More options Nov 30 2007, 11:11 pm
Newsgroups: microsoft.public.sqlserver.programming
From: "Tom Cooper" <tomcoo...@comcast.no.spam.please.net>
Date: Fri, 30 Nov 2007 23:11:11 -0500
Local: Fri, Nov 30 2007 11:11 pm
Subject: Re: when calling UPDATE from the result set of a SELECT statement, is the order in which rows from the SELECT statement 100% geronteed?
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author
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" <softwareengineer98...@yahoo.com> wrote in message

news:OIc2XN8MIHA.1188@TK2MSFTNGP04.phx.gbl...


    Reply to author    Forward  
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Tony Rogerson  
View profile  
 More options Dec 1 2007, 6:41 am
Newsgroups: microsoft.public.sqlserver.programming
From: "Tony Rogerson" <tonyroger...@torver.net>
Date: Sat, 1 Dec 2007 11:41:29 -0000
Local: Sat, Dec 1 2007 6:41 am
Subject: Re: when calling UPDATE from the result set of a SELECT statement, is the order in which rows from the SELECT statement 100% geronteed?
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author

> 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]


    Reply to author    Forward  
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
DR  
View profile  
 More options Dec 3 2007, 2:59 pm
Newsgroups: microsoft.public.sqlserver.programming
From: "DR" <softwareengineer98...@yahoo.com>
Date: Mon, 3 Dec 2007 11:59:43 -0800
Local: Mon, Dec 3 2007 2:59 pm
Subject: Re: when calling UPDATE from the result set of a SELECT statement, is the order in which rows from the SELECT statement 100% geronteed?
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author
i think your an idiot if you realy want to know what i think lol

"--CELKO--" <jcelko...@earthlink.net> wrote in message

news:330376b1-338d-4294-9c75-7841c098e547@d61g2000hsa.googlegroups.com...


    Reply to author    Forward  
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
--CELKO--  
View profile  
 More options Dec 3 2007, 3:20 pm
Newsgroups: microsoft.public.sqlserver.programming
From: --CELKO-- <jcelko...@earthlink.net>
Date: Mon, 3 Dec 2007 12:20:26 -0800 (PST)
Local: Mon, Dec 3 2007 3:20 pm
Subject: Re: when calling UPDATE from the result set of a SELECT statement, is the order in which rows from the SELECT statement 100% geronteed?
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author

>> 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"


    Reply to author    Forward  
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
End of messages
« Back to Discussions « Newer topic     Older topic »

Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2009 Google