Subject Re: [firebird-support] Question re: inserting data w/ Select and Concat
Author Helen Borrie
At 06:41 PM 2/07/2007, you wrote:
>i am new to firebird, and as i have been trying to insert data on my
>newly created table, i tried this sql:
>insert into titles(title, film_type)
>select title||'-2', film_type
>from titles;
>but it does not work, and this error message is displayed: 'error:
>arithmetic expression, numeric overflow or string truncation'.
>is there any way to use the select statement with concatenation in
>order to insert data? query is going to go wild, running continually
through the table (including newly inserted rows) until eventually it
falls over when one more '-2' concatenation overflows the Title field
(which is what you are seeing).

You'll need to do something to prevent this, e.g.

insert into titles(title, film_type)
select title||'-2', film_type
from titles
where titles not like '%-2';

This isn't exactly well-performing SQL - good enough for a one-off
run but, if it is something you plan to do frequently, write a stored
procedure to do it.