update w/ join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

update w/ join

Author

Topic

whaley
Starting Member

2 Posts

Posted - 2008-07-10 : 13:21:43

If using the select statement below, what would be the best way to write an update statement to update db_UserProfile with the values entered in fields for columns b.PropertyValue, c.PropertyValue, and d.PropertyValue. Thanks in advance for any help.

--------------------------------------------------------------

Schema

db_Users
UserID|Username|FirstName|LastName|Email

db_UserProfile
ProfileID|UserID|PropertyDefinitionID|PropertyValue

--------------------------------------------------------------

Select Statement

Select a.UserID as 'UserID', a.LastName as 'Last', a.FirstName as 'First', b.PropertyValue as 'Return', c.PropertyValue as 'Comment', d.PropertyValue as 'Status'
from db_Users a
left join db_UserProfile b
on a.UserID = b.UserID
left join db_UserProfile c
on a.UserID = c.UserID
left join db_UserProfile d
on a.UserID = d.UserID
where b.PropertyDefinitionID = 42
and c.PropertyDefinitionID = 41
and d.PropertyDefinitionID = 40
order by a.lastName, a.firstName
--------------------------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 14:33:42

Didnt understand what you're asking for. update which table? Dont db_UserProfile already have the PropertyValues? or was your question to flatten out data into different columns like cross tab reports? if thats what you want you can do it like this

SELECT a.UserID as 'UserID', a.LastName as 'Last', a.FirstName as 'First',
MAX(CASE WHEN b.PropertyDefinitionID = 42 THEN b.PropertyValue ELSE NULL END) AS Return,
MAX(CASE WHEN b.PropertyDefinitionID = 41 THEN b.PropertyValue ELSE NULL END) AS Comment,
MAX(CASE WHEN b.PropertyDefinitionID = 40 THEN b.PropertyValue ELSE NULL END) AS Status
from db_Users a
left join db_UserProfile b
on a.UserID = b.UserID
GROUP BY a.UserID, a.LastName, a.FirstName
ORDER BY a.LastName, a.FirstName

if not specify clearly which table you want to update

whaley
Starting Member

2 Posts

Posted - 2008-07-10 : 14:55:36

Thanks for reply. The select statement I use above returns exactly what I need when displayed in an asp form, with editable fields for Return, Comment and Status as seen here:

UserID Last First Return Comment Status
1 last1 first1 [ret1] [com1] [stat1]
2 last2 first2 [ret2] [com2] [stat2]

My question is, after editing the fields in the asp form, how do I write the sql statement to update db_UserProfile?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-12 : 01:28:13

quote:
Originally posted by whaley

Thanks for reply. The select statement I use above returns exactly what I need when displayed in an asp form, with editable fields for Return, Comment and Status as seen here:

UserID Last First Return Comment Status
1 last1 first1 [ret1] [com1] [stat1]
2 last2 first2 [ret2] [com2] [stat2]

My question is, after editing the fields in the asp form, how do I write the sql statement to update db_UserProfile?

what all fields will you be getting from the asp form?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources