update query with sub query - 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

General SQL Server Forums

New to SQL Server Programming

update query with sub query

Author

Topic

elelta
Starting Member

3 Posts

Posted - 2010-01-13 : 07:29:03

Hi,

I am trying to update once field in the 1st 100 records in a table with new data, these top 100 records also need to match a where condition to work but I cannot get it to work. can some look at my query?

update top (100) clients
set clientResearchManagerGUID = '9c93a5b5-1572-4cee-91e0-da80208aa6af'
where clientResearchManagerGUID='90b6b1e1-e63f-48a5-b2dd-069535d38ec1'

I did also try

update top (100) clients
set clientResearchManagerGUID = '9c93a5b5-1572-4cee-91e0-da80208aa6af'
where (clientResearchManagerGUID='90b6b1e1-e63f-48a5-b2dd-069535d38ec1') and (companyGUID in (select top (100) * from clients where clientResearchManagerGUID='90b6b1e1-e63f-48a5-b2dd-069535d38ec1')

to see if I need to specify a further condition but no luck.

all help greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 07:30:42

what happened when you tried above query? did you get some error? or it didnt do update?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-13 : 07:33:25

For "first" to have a meaning, you must define what is the order sequence.

N 56°04'39.26"
E 12°55'05.63"

elelta
Starting Member

3 Posts

Posted - 2010-01-13 : 07:46:49

ok, so now this query works. this is odd. I am not going to try it for all the records I want to update..will let you know if I have an issue

UPDATE TOP (10) Clients
SET clientResearchManagerGUID ='9c93a5b5-1572-4cee-91e0-da80208aa6af'
where clientResearchManagerGUID ='90b6b1e1-e63f-48a5-b2dd-069535d38ec1'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 07:49:25

quote:
Originally posted by elelta

ok, so now this query works. this is odd. I am not going to try it for all the records I want to update..will let you know if I have an issue

UPDATE TOP (10) Clients
SET clientResearchManagerGUID ='9c93a5b5-1572-4cee-91e0-da80208aa6af'
where clientResearchManagerGUID ='90b6b1e1-e63f-48a5-b2dd-069535d38ec1'

Ok Great
but keep in mind that it just updates 10 random records without any definite order. there's no concept of first and last in sql table so if you want to specify order you need to do that by means of order by

elelta
Starting Member

3 Posts

Posted - 2010-01-13 : 08:07:46

thanks. I actually dont need there to be an order and random 10 will work for my purposes.
:-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 08:09:47

quote:
Originally posted by elelta

thanks. I actually dont need there to be an order and random 10 will work for my purposes.
:-)

Ok then its fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources