BoogaWooga February 2016

How can I stop all rows from updating where conditions are met

I'm working on an assignment for college and have a PatientAdmissionsTbl, it stores all patient admissions in to various wards and has a DateDischarged column that shows when the patient was discharged from that ward.

update dbo.PatientAdmissionsTbl
set DateDischarged = getdate()
where PatientID = @PatientID
and WardID = @oldWard

I'm using this update to try and update the last row in the table that matches these criteria, the problem I'm having is that it's updating all rows where the patient and ward id's are(past and present) in the table but I have no idea how to just limit it to updating the last row that matches it.

So, my question is how can I stop all rows being updated?

This is the table being used, rows 27-34 are the ones that matter, the DateDischarged times match up when I try to do the update above, what I want is the current time and date to be in the last row matching the where clause and just the last row not the ones above it that match... Sorry, I'm not quite sure how to word this properly

edit: DateDischarged will be NULL until that Patient is discharged, the table is then updated and the current date and time are placed in the DateDischarged column where patientid and wardid matches up with the where clause

Answers


Gordon Linoff February 2016

One method in SQL Server is to use TOP with ORDER BY. Assuming you want the record with the most recent date discharged:

with topudate as (
      select top 1 pa.*
      from dbo.PatientAdmissionsTbl pa
      where PatientID = @PatientID and WardID = @oldWard
      order by DateDischarged desc
    )
update toupdate
    set WardId = @newWard;  -- WHATEVER THE COLUMNS YOU WANT TO UPDATE WITH THEIR VALUES


BoogaWooga February 2016

Adding: and DateDischarged is null to the where clause seems to have fixed it, I've probably made this sound more complicated than it actually was.

Post Status

Asked in February 2016
Viewed 3,237 times
Voted 10
Answered 2 times

Search




Leave an answer