Smallint stayed null after executing update SQL statement in Sybase

Question

The DB was Sybase. I executed update SQL statement like

UPDATE table_name SET smallint_col = 0, 
datetime_col = CONVERT(datetime, '1999-09-09') WHERE some_col = 'sth';

to set a null smallint attribute into a nonnull value.

I checked the update result with select statement right after the update statement via JDBC.

The attribute stayed null. Only the datetime attribute have been updated to intended value.

Also, here are what I have tried:

1. Set the target value to a nonnull value larger than 0. Still same result as above.
2. Added and smallint_col IS NULL in the WHERE clause. This time the update of both attributes failed.

Is there anything wrong with my update SQL statement?

What could be a possible reason for this?

Is there any possibility that the update of a certain attribute of a table is restricted?

Btw, I can only access to the DB with JDBC and I am trying to avoid talking to the DBA.

Answer 1

I just checked below whole SQL statements under Sybase ASE database, there is no problem on the update operation. If you are using JDBC call, you can get the return count of the update SQL execution. to check the affected count.

create table table_name (id int not null, smallint_col smallint null, 

datetime_col datetime, some_col varchar(32) null)
go

insert into table_name values(1, null, '2020-05-05', 'sth')
go

select * from table_name
go

UPDATE table_name SET smallint_col = 0, 
datetime_col = CONVERT(datetime, '1999-09-09') WHERE some_col = 'sth'
go

select * from table_name
go

Please double check your table definition. Is it the same as the above?