"Simple" query help

9 Dec 2005 11:16 AM

Magnus Blomberg

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

11 Dec 2005 12:00 AM

Hugo Kornelis

On Fri, 9 Dec 2005 12:16:41 +0100, Magnus Blomberg wrote:

Show quote

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