CODEWITHSUNDEEP
sqlsql-serversql-server-2008sybasesqlanywhere
Jasmine
Jasmine

Reputation: 5326

Deleting two rows based on successful deletion of another row which is based on a column condition - Sybase

I am trying to delete two rows based on only if another 3rd row is deleted. Now, the catch here is, the 3rd row has a condition where only for value "A" of column "MAN", it should delete that corresponding row.

i am doing it in Sybase (SQL Anywhere using Interactive SQL IDE).

I am unable to use @@ROWCOUNT.

Could someone please give me the query for this or how to achieve this please.

ALGORITHM:

DELETE FROM EFS WHERE NAME='MAN' AND VAL='A'

If (DeleteSuccessful) { DELETE FROM EFS WHERE NAME='MAN_1' AND NAME='MAN_2' }

I want to achieve this in a single query in Sybase.

ALTERNATIVE APPROACH:

I can also think of achieve this by first checking the value of VAL column and if it is XX, then I can write a query to delete all 3 rows using WHERE NAME ='MAN AND NAME ='MAN_1' AND NAME ='MAN_2'. this is also one approach. But don't know how to do it using syntax in Sybase in a single query/

Upvotes: 1

Views: 127

Answers (2)

crizzo
crizzo

Reputation: 31

I believe both you and valex are both getting the correct result, but your syntax is a little off. Also better to use IN vs. OR for performance.

IF EXISTS (SELECT * FROM EFS WHERE NAME='MAN' AND VAL='A') 
BEGIN
  DELETE FROM EFS WHERE NAME IN ('MAN','MAN_2','MAN_3')
END

And, I think you can use @@rowcount, perhaps you don't consider this a single statement approach but it also works, e.g.

select * from tempdb..test
irecord
1
2
3
99

declare @deleted INT
delete from tempdb..test where irecord = 3
select @deleted = @@rowcount
if @deleted > 0
begin
  print 'deletion detected'
  delete from tempdb..test where irecord IN (1,2)
end
else
  print 'no deletion detected'

1 row(s) affected.
1 row(s) affected.
deletion detected
2 row(s) affected.

select * from tempdb..test
irecord
99

Upvotes: 1

valex
valex

Reputation: 24144

I think you should check if these values exists and do it in one query

DELETE FROM EFS 
  WHERE (NAME='MAN' AND VAL='A')
        OR
        ( 
          (NAME='MAN_1' AND NAME='MAN_2')
          AND 
          EXISTS (SELECT * FROM EFS WHERE NAME='MAN' AND VAL='A')
         )

Upvotes: 1

Related Questions