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...

soozhCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SaldavoCommented:
Do you mean?
DELETE FROM table_name WHERE OriginalVisit IS NULL

Open in new window

0
wolfman007Commented:
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

0
soozhCEOAuthor Commented:
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".
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

SaldavoCommented:
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

0
pivarCommented:
Hi,

Is this what you require? You need to change ID to the unique patient id.

DELETE FROM yourtable WHERE NOT EXISTS (SELECT * FROM yourtable y2 WHERE y2.ID=yourtable.ID AND y2.Originalvisit='true')

/peter
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
soozhCEOAuthor Commented:
you understood first.... and as I said it was easy..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.