Hello,

I need a little help with a query I am trying to build. Heres what I
want it to do:

I have 2 tables, ActivityPartyBase and ActivityPointerBase (Microsoft
CRM database)

I want to update a cell (ParticipationTypeMask) in ActivityPartyBase
ONLY If the following 3 creds are matched:

1. ActivityTypeCode in ActivityPointerBase = 4201
2. ActivityID = ActivityID (The ActivityID should match in both
tables)
3. ActivityPartyID in ActivityPartyBase = OwningUser in
ActivityPointerBase

IF All 3 of these match, then update the ParticipationTypeMask.
Otherwise, skip it and move on.

I can't seem to build the query the way I need to and I'm not as
fluent in SQL As I should be.

Any ideas?

Thanks,

Ken

[quoted text, click to view]

I think this will do it:

update ActivityPartyBase
set ParticipationTypeMask = <whatever>
from ActivityPartyBase pa
join ActivityPointerBase po on pa.ActivityID = po.ActivityID
and pa.ActivityPartyID = po.OwningUser

[quoted text, click to view]

Thanks. This returned "0 rows affected" when ran.

I think I need a loop with an IF statement in it, something like:

for i = <number of rows>
IF pa.ActID = po.ActID AND ActPartID = OwnUser AND ActTypeCode = 4201
set PartTypeMask
endif
next i

(I abbreviated there). Don't know how to write that into an SQL query.

[quoted text, click to view]

As a sanity check, can you post some sample data?

[quoted text, click to view]

SQL has something called cursors, but they should be avoided

[quoted text, click to view]

This is from the ActivityPartyBase table:

The data below is from a table with the following columns. The info
below is the data.
ActivityID
ActivityPartyID
PartyID
PartyObjectTypeCode
ParticipationTypeMask


{C08B545D-42ED-DB11-BC3E-000E0CC4B9D1}
{8CDE8C60-48E4-4F40-A9E1-00024AB4909C}
{1E5566A9-11ED-DB11-8B77-000E0CC4B9D1}
8
9

{26346169-2CED-DB11-8B77-000E0CC4B9D1}
{ACDD34A8-D4F9-404B-86C7-000A5E3042FC}
{24346169-2CED-DB11-8B77-000E0CC4B9D1}
3
8

{A32ACAC9-2CED-DB11-8B77-000E0CC4B9D1}
{A52ACAC9-2CED-DB11-8B77-000E0CC4B9D1}
{D71A4249-1AED-DB11-8B77-000E0CC4B9D1}
2
8

{A32ACAC9-2CED-DB11-8B77-000E0CC4B9D1}
{A62ACAC9-2CED-DB11-8B77-000E0CC4B9D1}
{FB5466A9-11ED-DB11-8B77-000E0CC4B9D1}
8
9

The next set of data is from the ActivityPointerBase Table:

ActivityID
ActivityTypeCode
OwningUser

{9004E737-35ED-DB11-AC60-00038A000015}
4202
{F15466A9-11ED-DB11-8B77-000E0CC4B9D1}

{BA786DA7-25ED-DB11-8B77-000E0CC4B9D1
4201
{DD5466A9-11ED-DB11-8B77-000E0CC4B9D1}

{BC786DA7-25ED-DB11-8B77-000E0CC4B9D1}
4201
{055566A9-11ED-DB11-8B77-000E0CC4B9D1}

{BE786DA7-25ED-DB11-8B77-000E0CC4B9D1}
4212
{FB5466A9-11ED-DB11-8B77-000E0CC4B9D1}

{C0786DA7-25ED-DB11-8B77-000E0CC4B9D1}
4201
{1E5566A9-11ED-DB11-8B77-000E0CC4B9D1}

{C2786DA7-25ED-DB11-8B77-000E0CC4B9D1}
4201
{1E5566A9-11ED-DB11-8B77-000E0CC4B9D1}

{C4786DA7-25ED-DB11-8B77-000E0CC4B9D1}
4201
{FB5466A9-11ED-DB11-8B77-000E0CC4B9D1}

{C6786DA7-25ED-DB11-8B77-000E0CC4B9D1}
4201
{FB5466A9-11ED-DB11-8B77-000E0CC4B9D1}

{C8786DA7-25ED-DB11-8B77-000E0CC4B9D1}
4201
{FB5466A9-11ED-DB11-8B77-000E0CC4B9D1}

{CA786DA7-25ED-DB11-8B77-000E0CC4B9D1}
4201
{DD5466A9-11ED-DB11-8B77-000E0CC4B9D1}

I took the top 10 items from the table, and removed all the "non-
pertinent" cells. Let me know if this helps.

thanks,

Ken