"Simple" query help

Hello!
I would like some help to figure out how to write a specific update query.
What I'm trying to do:
I have one table with these columns:
no (primary key or clustered?)
object (primary key or clustered?)
cid
usergroup1
usergroup2
Lets say the table I would like to upgrade is called obj1 and the table I'm
getting the value to set from is called obj2.
This is in real the same table and it's called objectx.
I would like to set cid for the records with no=7 in obj1 to the value of
the records in obj2 with:
obj2.no=1,
obj2.usergroup2=obj1.object
the value should be set to obj2.usergroup1
The obj2.usergroup2 values could be found several times.
Any idea how to write this query?
Regards MagnusOn Fri, 9 Dec 2005 12:16:41 +0100, Magnus Blomberg wrote:

>Hello!
>I would like some help to figure out how to write a specific update query.
>What I'm trying to do:
>I have one table with these columns:
>no (primary key or clustered?)
>object (primary key or clustered?)
>cid
>usergroup1
>usergroup2
>Lets say the table I would like to upgrade is called obj1 and the table I'm
>getting the value to set from is called obj2.
>This is in real the same table and it's called objectx.
>I would like to set cid for the records with no=7 in obj1 to the value of
>the records in obj2 with:
>obj2.no=1,
>obj2.usergroup2=obj1.object
>the value should be set to obj2.usergroup1
>The obj2.usergroup2 values could be found several times.
>Any idea how to write this query?
>Regards Magnus
>
Hi Magnus,
Before writing the query, the specifications should be clear. You say
that obj2.usergroup2 can be found several times. That means that there
might be more than one obj2.usergroup1. Which one of these should be
used to set obj1.cid'
If you need the lowest value, try if this works:
UPDATE objectx
SET cid = (SELECT MIN(obj2.usergroup1)
FROM objectx AS obj2
WHERE obj2.no = 1
AND obj2.usergroup2 = objectx.object)
WHERE no = 7
(untested - if you prefer a tested reply or if this doesn;t work, then
please check www.aspfaq.com/5006 to find out how to provide clear specs
and test data).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)