2

As you see from these images, the ROSHEADERID column is invalid and when I execute the first one it returns a meaningful message. But when I use this query in where clause as a subquery. It executes and deletes all of the records without warning or aborting the operation.

How can this be ?

enter image description here

enter image description here

Davut Gürbüz
  • 5,526
  • 4
  • 47
  • 83

2 Answers2

2

The subquery has access to columns in outer query, thus column ROSHEADERID you ask for is effectively taken from EXM_REVIEWOFSYSTEMS (not from EXM_REVIEWOFSYSTEMSHEADER), thus deleting all records in outer table.

This should clarify a bit on what's going on behind the scenes:

http://sqlfiddle.com/#!2/623c7/3

More information here: http://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx

Kuba Wyrostek
  • 6,163
  • 1
  • 22
  • 40
1

Use Alias Names for tables to avoid conflict: 

BEGIN TRAN
   DELETE   FROM EXM_REVIEWOFSYSTEMS
   WHERE    ROSHEADERID IN ( SELECT rsh.ROSHEADERID
                             FROM   EXM_REVIEWOFSYSTEMSHEADER rsh
                             WHERE  rsh.PATIENTID = '' )
   ROLLBACK TRAN
Vasily
  • 5,707
  • 3
  • 19
  • 34
knkarthick24
  • 3,106
  • 15
  • 21
  • You are right. I thought the subquery itself in execution plan then the outer. But in execution plan the first thing is parsing the statement. – Davut Gürbüz Jan 12 '15 at 12:59