Updating multiple rows for user – Setting 1 row as “Default” Yes, and other to be No

Lets assume the following data selected from SQL TBL_Profiles:

No	User	Profile	Default
1	User1	Profile1	Yes
2	User1	Profile2	No
3	User1	Profile3	No

Currently, User1 default profile is Profile1. That’s how it was setup initially when profiles were created. If User1 decided at a later stage to change his/her default profile, that means the newly selected profile should update Default = Yes, and the rest update Default = No. I can do that by selecting all profiles, iterate then update. However, I’m looking more into the easiest concept to do so. Hope I’m clear (which I doubt, since the idea is still vague in my little brain).

Many thanks.

Answer

The problem is with this data structure, it’s better to hold 1 foreign key “Default Profile” in some upper lever table (perhaps in Users table).

But anyway, if you must apply multiple changes, I suggest to execute an SQL update statement, like this:

UPDATE  TBL_Profiles
SET     [Default] = CASE WHEN T.[Profile] = @SelectedProfile THEN 1 ELSE 0 END
FROM    TBL_Profiles AS T
WHERE   T.[User] = @User

and don’t forget to pass the 2 parameters @User and @SelectedProfile

let me know if it answers your question, or if it needs some adjustments