• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Update table based on value in related table

I have 2 tables: aspnet_Users and tblUserProfileData.

Who can I update all users in tblUserProfileData that has one spesific ApplicationId in aspnet_users? The relation between the tables are tblUserProfileData.UserId and aspnet_Users.UserId.

I need to set tblUserProfileData.Payment = '0' where aspnet_Users.ApplicationId = 'aca98bd7-4fee-4bf3-7d21-3b88c9457d57'


This is a select statement that returns all users where I need to set tblUserProfileData.Payment = '0':

SELECT     tblUserProfileData.UserId, tblUserProfileData.Payment, aspnet_Users.ApplicationId
FROM         tblUserProfileData INNER JOIN
                      aspnet_Users ON tblUserProfileData.UserId = aspnet_Users.UserId
WHERE     (aspnet_Users.ApplicationId = 'aca98bd7-4fee-4bf3-7d21-3b88c9457d57')
0
webressurs
Asked:
webressurs
  • 2
  • 2
1 Solution
 
Pratima PharandeCommented:
Update A

Set A.Payment = '0'
FROM         tblUserProfileData A INNER JOIN
                      aspnet_Users ON A.UserId = aspnet_Users.UserId
WHERE     (aspnet_Users.ApplicationId = 'aca98bd7-4fee-4bf3-7d21-3b88c9457d57')
0
 
webressursAuthor Commented:
pratima_mcs: When I run the SQL I get the following error:

Column or expression 'Payment' cannot be updated.
SQL Execution Error.
Error Source: .Net.SqlClient Data Provider
Error Message: Invalid object name 'A'

Any clue?
Thanks :)
0
 
Pratima PharandeCommented:
are you sure you put the table alias A

Update A

Set A.Payment = '0'
FROM         tblUserProfileData A INNER JOIN
                      aspnet_Users ON A.UserId = aspnet_Users.UserId
WHERE     (aspnet_Users.ApplicationId = 'aca98bd7-4fee-4bf3-7d21-3b88c9457d57')
0
 
webressursAuthor Commented:
Thank you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now