Ad
Run Multiple Update Statements On Same Table In Stored Procedure Using Values From Table Vlued Parameter
I want to update a column in a table uing values from a table valued parameter, then I want to update another column (same rows) depending on the value of the column that was updated on first statement. Here is the code:
@reports as WhatsTableType ReadOnly
BEGIN
update dbo.tblappointments Set dbo.tblappointments.D_report = r.D_report
from dbo.tblappointments as A inner join @reports as r on A.appointmentID = r.appointmentID;
update dbo.tblappointments Set dbo.tblappointments.WantSMS = 0
from dbo.tblappointments as A inner join @reports as r on A.appointmentID = r.appointmentID
where A.D_report = 'Read' or A.D_report = 'DEVICE';
END
The table parameter contain two columns (appointmentID, D_report). I guess I can use single update statement using IIF, I'm not sure about the best way to do this.
Thank you.
Ad
Answer
Depending on what you want WantSMS
to become if D_Report
is something else, or NULL, or if it should depend on the existing value in the table, you can do this in one statement as follows:
UPDATE A Set
A.D_report = r.D_report,
A.WantSMS = CASE
-- need to check R for the _new_ value:
WHEN r.D_Report IN ('Read', 'DEVICE') THEN 0
ELSE A.WantSMS END
FROM dbo.tblappointments as A
INNER JOIN @reports as r
ON A.appointmentID = r.appointmentID;
IIF
is just fancy CASE
but I find CASE
a lot more flexible. YMMV.
Ad
source: stackoverflow.com
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key
Ad