DELETE based on 2 tables

Ashok
Ashok used Ask the Experts™
on
select a.*
from temp1 a, patient b
WHERE a.chart_no = b.chart_no and
b.group_assignment = 'AAA'

I have 66 records in temp1
and
above query returns 14 records.

How do I delete only 14 records from temp1?

Thanks,
Ashok
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
Try:
delete temp1
from temp1 a, patient b
WHERE a.chart_no = b.chart_no and
b.group_assignment = 'AAA'

Open in new window

Som TripathiDatabase Administrator

Commented:
Ashok,
Not sure with the question.
Anuradha GoliSystems Development / Support Specialist
Commented:
DELETE	FROM	TEMP1
WHERE	CHART_NO 
IN	(
		SELECT	A.CHART_NO  
		FROM	TEMP1 A, 
				PATIENT B
		WHERE	A.CHART_NO = B.CHART_NO 
		AND		B.GROUP_ASSIGNMENT = 'AAA'
	)

Open in new window

Commented:
delete temp1
from temp1 a inner join patient b
on a.chart_no = b.chart_no and
b.group_assignment = 'AAA'
AshokSr. Software Engineer

Author

Commented:
wdosanjos,

I though I already tried what you have suggested,
but I guess in a hurry, I must have selected wrong (partial) delete
statement so it deleted all 66 records.

It is very simple to change UPDATE to DELETE.

Thanks,
Ashok

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial