insert/update change one column

Classic Servoy
1

I’m looking to copy and insert multiple rows from the same table, changing the data in one column on the insert. I’m running into a conflict with the ‘record_id’ - I’ve also tried this code below without the record_id - I’m getting either “record_id already exists” or “not-null constraint”

insert into table1 (record_id, columnA, columnB, columnC)
select record_id, columnA, columnB, '123456789'
from table1
where record_id  between 1710330 and 1710500

Thanks in advance for any suggestions.

2

Hi Bob,

Your query selects and then inserts the record_id value, so this will trigger the unique constraint.
Now if you don’t select/insert the record_id column and you don’t use database sequences/identities then you get a not-null constraint error.

So the fix is to make the PK value database managed (db identity or db sequence, depending on the db vendor) and then use the following SQL:

INSERT into table1 (columnA, columnB, columnC)
SELECT columnA, columnB, '123456789'
FROM  table1
WHERE record_id BETWEEN 1710330 AND 1710500

Hope this helps.

3

Thanks Robert

I’ll give that a shot