asked
Scan Table for Multiple Values and Perform Update on another Table
I have two tables. Users and UserInformation UserID is a primary key in Users and a foreign key in UserInformation There is only one user in the User Table but there can be multiple rows linked to a single User in the User Information table. What I want to do is scan the columns in UserInformation.TxtField4 for specific values and update For example if UserInformation.TxtField4 had 'Test' on one row and 'SQL' for another row I'd want to update User.TxtField1 with 'Valid'. If none of these rows had either of these values I'd want to update user.TxtField1 with 'Invalid' Can anyone help?
Get the bits working first, then combine them. Write a query to get UserIDs where they have both rows in UserInformation. Then you can left join that to the Users table, and update TxtField1 depending on whether the userInformation query returns this User's UserID. Here's a pseudo-code outline: update u set TxtField1 = case when ui.UserID is not null then 'Valid' else 'Invalid' end from Users u left join ( -- Get the UserID where both records found in UserInformation select UserID from UserInformation etc ) ui on ui.UserID = u.UserID;
19 People are following this question.
