Link to home
Start Free TrialLog in
Avatar of soozh
soozhFlag for Sweden

asked on

Very simple SQL Server SQL question

Hello,

I have a dataset that I contains patient records.  There are many records for the same patient.  Each record contains details about a visit to the hospital.

I would like to eliminate the records for those patients that do not have thier first visit at the hospital.  There is a column called OriginalVisit which is set true for the patients first visit.

Patients that did not have thier first visit at the hospital do not have a record with OrginalVist set true.

So I need a delete statement along the lines of:

Delete from MyTable where "the patient does not have a record with OriginalVisit set true".

Must be easy...

Avatar of Saldavo
Saldavo
Flag of Netherlands image

Do you mean?
DELETE FROM table_name WHERE OriginalVisit IS NULL

Open in new window

If OriginalVisit is a bit field then the following should work.

But why do you not just select the data you need first to make sure that you are getting the right data first before deleting.
DELETE FROM MyTable
WHERE OriginalVisit = 0
 
SELECT * FROM MyTable
WHERE OriginalVisit = 0
 
//or
 
SELECT * FROM MyTable
WHERE OriginalVisit = 1
 
//for the patients whose first visit was at your hospital

Open in new window

Avatar of soozh

ASKER

I did not perhaps make it clear but only the first visit has the OriginalVisit set to true.  The remaining records for the patient visits have it set false.

I want to keep only the records where the patient had an original visit at the hospital AND I want to keep all that patients visits.
Using a delete that tests all records for OriginalVisit to be false will also remove all the other visits a patient has done.

If you look at my pseudo code you will see that i want to delete the records where the patient does not have an original visit set true, but i still want to keep all the records for the patinet that does have a original visit set to true.

Pseudo code:
Delete from MyTable where "the patient does not have a record with OriginalVisit set true".
To make it some more clear in a few words:

# You want to delete the records where OriginalVisit is empty?
# You want to delete the records where OriginalVisit is 'False'?
# You want to delete the records where OriginalVisit is 0?
DELETE FROM table_name WHERE OriginalVisit IS NULL
 
DELETE FROM table_name WHERE OriginalVisit = 'False'
 
DELETE FROM table_name WHERE OriginalVisit = 0

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of pivar
pivar
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of soozh

ASKER

you understood first.... and as I said it was easy..