Updating a table if another tables row matches... not sure if that quite explains it.

[kwcraft]

I have 2 tables in my SQL Server (These were created by Microsoft CRM)

Table 1 is ActivityPointerBase (po for short)
Table 2 is ActivityPartybase (pa for short)

First:

Loop begins with grabbing the first row from po.
IF TypeCode = 4201 great! Start the second loop
grab the first row of pa and test
if po.activityID = pa.activityID AND po.OwningUser = pa.ActivityPartyID
Then we set the pa.ParticipationTypeMask = 7
Otherwise, endif and goto next row
If TypeCode did not equal 4201 we would go to the next row.

I've got the idea down, I know how it SHOULD work. I just do not know how to code it so that it WILL work. I keep getting random errors on all of my attempts.

Any ideas?

thanks,

Ken

[pkreemer]

Hi kwcraft, you can do this with a SQL update statement. Just to be sure, TypeCode is in the po table?

Try this:

UPDATE ActivityPartybase 
   SET  ParticipationTypeMask = 7
      FROM ActivityPointerBase po
         WHERE 
            po.TypeCode = 4201 
            AND po.activityID = ActivityPartybase.activityID
            AND po.OwningUser = ActivityPartybase.ActivityPartyID

You describe your algorithm in a procedural way and SQL works in a different, set-oriented manner. So you just have to translate. It's not always as easy as this but I think the SQL above is what you want.

I did not test it with dummy tables, so let me know if I got something wrong!

Paul