DELETE statement not working

deletesybasesybase-ase

I'm running my database on a Sybase ASE 15.7 server.
A simple delete statement fails without printing any error , I presume there is no permissions problem as I used to delete rows by the past.
Here is what I did from an isql console.

1> select count(*) from LOGIN where login="336959686@login.com"
2> go

 -----------
       1

(1 row affected)
1> delete from LOGIN where login="336959686@login.com"
2> go
(0 rows affected)

The row wasn't deleted, am I missing something?

LOGIN table definition :

1> sp_help LOGIN
2> go
 Name  Owner Object_type Object_status Create_date
 ----- ----- ----------- ------------- -------------------
 LOGIN xxx   user table   -- none --   Dec  7 2010  2:13PM

(1 row affected)
 Column_name            Type     Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ---------------------- -------- ------ ---- ----- ----- -------------- ------------ --------- ---------------- ---------------------- ----------
 login_oidval           numeric       8   16     0     0              0 NULL         NULL      NULL             NULL                            0
 login                  varchar     165 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 creation_date          datetime      8 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 last_modification_date datetime      8 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 idp_oidval             smallint      2 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
Object has the following indexes

 index_name index_keys         index_description    index_max_rows_per_page index_fillfactor index_reservepagegap index_created       index_local
 ---------- ------------------ -------------------- ----------------------- ---------------- -------------------- ------------------- ------------
 PK_LOGIN    login_oidval      nonclustered, unique                       0                0                    0 Dec 10 2010  7:10PM Global Index
 login_idx1  login, idp_oidval nonclustered, unique                       0                0                    0 Jun 10 2011  2:55PM Global Index

(2 rows affected)
 index_ptn_name       index_ptn_seg
 -------------------- -------------
 PK_LOGIN_448001596   default
 login_idx1_448001596 default

(2 rows affected)
No defined keys for this object.
 name  type       partition_type partitions partition_keys
 ----- ---------- -------------- ---------- --------------
 LOGIN base table roundrobin              1 NULL

(1 row affected)

 partition_name  partition_id compression_level pages row_count segment create_date
 --------------- ------------ ----------------- ----- --------- ------- -------------------
 LOGIN_448001596    448001596 none                863     25497 default Feb 12 2014  3:01PM

 Partition_Conditions
 --------------------
 NULL

 Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)              Ratio(Min/Avg)
 ----------- ----------- ----------- --------------------------- ---------------------------
         863         863         863                    1.000000                    1.000000
Table LOB compression level
Lock scheme Datarows
The 'ascinserts' attribute is not applicable to tables with datarow or datapage lock schemes.

 exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts
 ------------ -------------- ---------- ----------------- ------------ -----------
            0              0          0                 0            0           0

(1 row affected)
 concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
 ------------------------- --------------------- -------------------
                        15                     0                   0
(return status = 0)

Best Answer

Instead of using double-quotes, try using single-quotes:

DELETE 
FROM LOGIN 
WHERE login = '336959686@login.com';

If the delete statement produces no error but doesn't appear to delete the row affected, that means one of the following:

  1. The criteria (the WHERE clause) is not matching the desired row.

  2. You have a transaction that is rolling back the delete, or has not yet committed the delete. Check the transaction chaining mode using SELECT @@tranchained; Perhaps try this:

    SELECT *
FROM LOGIN 
WHERE login = '336959686@login.com';
BEGIN TRANSACTION
DELETE 
FROM LOGIN 
WHERE login = '336959686@login.com';
COMMIT TRANSACTION
SELECT *
FROM LOGIN 
WHERE login = '336959686@login.com';

  3. Undetected database corruption - this is extremely rare. Have you performed DBCC CHECKDB lately? Make certain you have a reliable backup. Test the backup by restoring it onto another instance of Sybase ASE. If you have done that, running DBCC CHECKDB should be performed. It may heavily impact I/O, so I would run it during non-peak hours.

Related Question