Delete statement

• khushbu

  SSCarpal Tunnel

  Points: 4591

  More actions

  April 21, 2017 at 3:22 am

  #337328

  Can someone help me in writing this query. Doing some silly mistake but not able to recognize.
  for testing purpose commented actual delete clause

  if DB_name () = N'MyDB'
  begin

  IF( EXISTS( SELECT 1 FROM sch1.table1 WHERE guid= N'05172A47' and IntId=12 ))

  print 'im deleting 1';

  --DELETE FROM sch1.table1 where Guid = N'05172A47' and IntId=12

  
  

  print 'im deleting 2';

  IF( EXISTS( SELECT 1 FROM sch1.table1 WHERE Guid = N'3DFB856F' and IntId=13 ))

  --DELETE FROM sch1.table1 where Guid = N'3DFB856F' and IntId=13

  end
  ELSE PRINT 'Nothing deleted'

  end

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  April 21, 2017 at 3:39 am

  #1939269

  khushbu - Friday, April 21, 2017 3:22 AM

  Can someone help me in writing this query. Doing some silly mistake but not able to recognize.
  for testing purpose commented actual delete clause

  if DB_name () = N'MyDB'
  begin

  IF( EXISTS( SELECT 1 FROM sch1.table1 WHERE guid= N'05172A47' and IntId=12 ))

  print 'im deleting 1';

  --DELETE FROM sch1.table1 where Guid = N'05172A47' and IntId=12

  
  

  print 'im deleting 2';

  IF( EXISTS( SELECT 1 FROM sch1.table1 WHERE Guid = N'3DFB856F' and IntId=13 ))

  --DELETE FROM sch1.table1 where Guid = N'3DFB856F' and IntId=13

  end
  ELSE PRINT 'Nothing deleted'

  end

  Something like this?
  😎
  IF DB_name () = N'MyDB'
BEGIN
  IF( EXISTS( SELECT 1 FROM sch1.table1 WHERE guid= N'05172A47' and IntId=12 ))
  BEGIN
   print 'im deleting 1';
   --DELETE FROM sch1.table1 where Guid = N'05172A47' and IntId=12
  END

    IF( EXISTS( SELECT 1 FROM sch1.table1 WHERE Guid = N'3DFB856F' and IntId=13 ))
  BEGIN
   print 'im deleting 2';
   --DELETE FROM sch1.table1 where Guid = N'3DFB856F' and IntId=13
  END
END
ELSE
BEGIN 
  PRINT 'Nothing deleted'
END

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  April 21, 2017 at 5:45 am

  #1939280

  A more efficient way of coding this is to perform the deletion and then check @@ROWCOUNT afterwards, thus hitting the table once per deletion rather than twice.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  April 21, 2017 at 6:46 am

  #1939295

  khushbu - Friday, April 21, 2017 3:22 AM

  Can someone help me in writing this query. Doing some silly mistake but not able to recognize.
  for testing purpose commented actual delete clause

  if DB_name () = N'MyDB'
  begin

  IF( EXISTS( SELECT 1 FROM sch1.table1 WHERE guid= N'05172A47' and IntId=12 ))

  print 'im deleting 1';

  --DELETE FROM sch1.table1 where Guid = N'05172A47' and IntId=12

  
  

  print 'im deleting 2';

  IF( EXISTS( SELECT 1 FROM sch1.table1 WHERE Guid = N'3DFB856F' and IntId=13 ))

  --DELETE FROM sch1.table1 where Guid = N'3DFB856F' and IntId=13

  end
  ELSE PRINT 'Nothing deleted'

  end

  Not sure if your GUID column is actually a uniqueidentifier or not, but if so I don't think either of your strings are valid. Also, I don't see where you stated what the actual problem/error is.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service