Hi

Let's say i have three databases:

main.dat: AutoInc Index Field + Name Field + Search + ReturnField
group.dat: AutoInc Index Field + Group Field
link.dat: Links Main Index to Group Index


Now if i want to set ReturnField to some value for all members in Group "TestGroup" (which has an index of 23) I can use:

UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN "link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID IN (23))


But how can i set ReturnField for all Members who are NOT in the group "TestGroup" (23)?

Naturally I can't use the following:

UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN "link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID NOT IN (23))

Because that would return Names that are in "TestGroup" (23) if they are also in another group.


Excuse my ignorance and thanks in advance for any advice.


Regards

Dave

Dave wrote:
....
> But how can i set ReturnField for all Members who are NOT in the
> group "TestGroup" (23)?
>
> Naturally I can't use the following:
>
> UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER
> JOIN "link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID NOT IN
> (23))

Try a LEFT OUTER JOIN with the Link table and add a WHERE L2.GroupID IS
NULL.

Ralf

Dave,

<< Now if i want to set ReturnField to some value for all members in Group
"TestGroup" (which has an index of 23) I can use:

UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN
"link.dat" L2 ON (M2.Index = L2.NameID)  WHERE (L2.GroupID IN (23)) >>

Actually, you shouldn't use a LOJ if you want to exclude those records in
main that don't have a matching record in link (given the IN (23)
condition).

<< But how can i set ReturnField for all Members who are NOT in the group
"TestGroup" (23)?

UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN
"link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID NOT IN (23))

Because that would return Names that are in "TestGroup" (23) if they are
also in another group. >>

Use an INNER JOIN with a <> condition:

UPDATE "main.dat" M2 set ReturnField=123
FROM "main.dat"
INNER JOIN "link.dat" L2 ON (M2.Index <> L2.NameID)
WHERE (L2.GroupID IN (23))

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim wrote:

> UPDATE "main.dat" M2 set ReturnField=123
> FROM "main.dat"
> INNER JOIN "link.dat" L2 ON (M2.Index <> L2.NameID)
> WHERE (L2.GroupID IN (23))

Thanks Tim, but have i got this code correct?

This sets every record in main.dat as 123.


Dave

Dave

> main.dat: AutoInc Index Field + Name Field + Search + ReturnField
> group.dat: AutoInc Index Field + Group Field
> link.dat: Links Main Index to Group Index
>
>
> Now if i want to set ReturnField to some value for all members in Group
"TestGroup" (which has an index of 23) I can use:
>
> UPDATE "main.dat" M2 set ReturnField=123 FROM "main.dat" LEFT OUTER JOIN
"link.dat" L2 ON (M2.Index = L2.NameID) WHERE (L2.GroupID IN (23))
>
>
> But how can i set ReturnField for all Members who are NOT in the group
"TestGroup" (23)?
>

How about

UPDATE "main.dat" SET ReturnField=123
WHERE Index NOT IN (SELECT NameId FROM "link.dat" WHERE GroupId = 23)

John

Dave,

<< Thanks Tim, but have i got this code correct?

This sets every record in main.dat as 123. >>

It shouldn't.  Attached is the table creation script and SQL that I used.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks John and Tim,

Tim, you were right, it does work.  However I went with John's suggestion because the INNER JOIN method failed (in that every record was set) if there were multiple items in the In clause (e.g.
WHERE (GroupID in (30,23)).  It seems just as quick too.

Regards

Dave

Dave,

<< Tim, you were right, it does work.  However I went with John's suggestion
because the INNER JOIN method failed (in that every record was set) if there
were multiple items in the In clause (e.g. WHERE (GroupID in (30,23)).  It
seems just as quick too. >>

Ahh yes, that would be an issue and I should have known that.  In that case,
yes the sub-select IN would work best.

--
Tim Young
Elevate Software
www.elevatesoft.com