CODEWITHSUNDEEP
javadatabaseh2
Philippe Billerot
Philippe Billerot

Reputation: 11

SQL update not working with integer ID

This command on SQL Server

UPDATE tbl SET name='Hi' WHERE id=''

works if 'id' is set as an integer value, but it does not work on H2.

What may be the solution?

Upvotes: 1

Views: 8491

Answers (3)

Thomas Mueller
Thomas Mueller

Reputation: 50127

H2 throws an exception because it can't convert the empty string '' to a number. H2 internally uses java.lang.Long.parseLong("") which fails with java.lang.NumberFormatException: For input string: "".

For the SQL script:

drop table tbl;
create table tbl(id int primary key, name varchar(255));
insert into tbl values(1, 'Hello');
UPDATE tbl SET name='Hi' WHERE id='';

H2 will throw the exception:

Data conversion error converting  [22018-161]

Most other databases (PostgreSQL, Apache Derby, HSQLDB) throw a similar exception.

You need to use a number, or IS NULL as in:

UPDATE tbl SET name='Hi' WHERE id IS NULL;

or

UPDATE tbl SET name='Hi' WHERE id = 0;

Upvotes: 0

nirav patel
nirav patel

Reputation: 465

UPDATE TEST SET NAME='Hi' WHERE ID='1'; that is working in sql server even if id field is integer but if you want to update the row where id is null then you have to use below statement : UPDATE TEST SET NAME='Hi' WHERE ID is Null; instead of UPDATE TEST SET NAME='Hi' WHERE ID ='';

And if id is varchar then you can use your statement to update the values where ID is not null and data is not available there. But if you want to update the values for record where NULL value of ID field then you have to use

UPDATE TEST SET NAME='Hi' WHERE ID is Null;

Upvotes: 4

Bohemian
Bohemian

Reputation: 425198

If ID is integer, you shouldn't use quotes for the value:

UPDATE TEST SET NAME='Hi' WHERE ID = 5; // not ID = '5'

Many databases will accept the quoted version, but are not required to by the SQL language specification.

Upvotes: 6

Related Questions