August 17, 2022 at 12:38 pm
Sorry for the title.
I am searching for the best soultion for the following problem:
I have a table1 with columns name1, name2, birthdate and FK_category.
Now, I want to update FK_category depending on name1 and name2
like this:
UPDATE table1 SET FK_category=1000 WHERE name1='John' and name2='Doe'
Easy. But I have to test against multiple conditions, like:
UPDATE table1 SET FK_category=1099 WHERE name1='Marylyn' and birthdate='whenever'
At the moment, I have a stored procedure with multiple lines of the above example.
This cannot be effective.
My real table has several columns more and the WHERE clause for each line may differ
Is there a more effective way of testing each line against multiple (changing) WHERE clauses?
Thank you
EDIT: Also the SET part may differ ! (diffenrent column to update)
In my wet drams I have a second
August 17, 2022 at 1:38 pm
Put the conditions in a separate table. Then use a JOIN to UPDATE the original table based on the matching condition, or the first matching condition if multiple conditions could be met.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 17, 2022 at 1:47 pm
A case statement should work if there aren't too many variations -- e.g., something like
UPDATE table1
SET FK_category = CASE WHEN name1='John' and name2='Doe' THEN 1000
WHEN name1='Marylyn' and birthdate='whenever' THEN 1099
ELSE FK_category
END;
If there are dozens or more then you may want to put those in a table and join to that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply