Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update table based on value in related table

Posted on 2011-09-26
4
Medium Priority
?
240 Views
Last Modified: 2012-05-12
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
Comment
Question by:webressurs
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36598063
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
 
LVL 1

Author Comment

by:webressurs
ID: 36598294
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 36598702
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
 
LVL 1

Author Closing Comment

by:webressurs
ID: 36707723
Thank you :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question