MySql Update Query is Deleting Rows

Question

I am trying to update rows (remove hyphen and after hyphen all characters from column str_column) using query -

UPDATE table_name
SET str_column = SUBSTRING(str_column, 0, LENGTH(str_column) - 2)
WHERE str_column = '561279-1';

but above query is deleting matched rows instead of updating.

I am not able to understand, how update query can delete rows??

Answer 1

The substring has to begin with 1 not with 0

in the manual is clearly stated

A value of 0 for pos returns an empty string.

example:

CREATE tABLE table_name (str_column varchar(20))

INSERT INTO table_name VALUES ('561279-1')

UPDATE table_name
SET str_column = SUBSTRING(str_column, 1, LENGTH(str_column) - 2)
WHERE str_column = '561279-1';

SELECT * FROM table_name

| str_column |
| :--------- |
| 561279     |

db<>fiddle here