471,082 Members | 908 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,082 software developers and data experts.

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

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
Apr 25 '07 #1
1 1249
Hi kwcraft, you can do this with a SQL update statement. Just to be sure, TypeCode is in the po table?

Try this:

Expand|Select|Wrap|Line Numbers
  1. UPDATE ActivityPartybase 
  2.    SET  ParticipationTypeMask = 7
  3.       FROM ActivityPointerBase po
  4.          WHERE 
  5.             po.TypeCode = 4201 
  6.             AND po.activityID = ActivityPartybase.activityID
  7.             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
Apr 25 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

61 posts views Thread by Toby Austin | last post: by
6 posts views Thread by Geoff | last post: by
4 posts views Thread by Geoff | last post: by
21 posts views Thread by Johan Tibell | last post: by
33 posts views Thread by bill | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.