insert/update change one column

Questions and answers regarding general SQL and backend databases

insert/update change one column

Postby bobm » Sun Feb 18, 2018 10:51 am

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"

Code: Select all
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.
Bob Mezzadri
EyeCOR By Nteon
bobm
 
Posts: 71
Joined: Thu Dec 29, 2011 8:24 pm

Re: insert/update change one column

Postby ROCLASI » Sun Feb 18, 2018 2:49 pm

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:
Code: Select all
INSERT into table1 (columnA, columnB, columnC)
SELECT columnA, columnB, '123456789'
FROM  table1
WHERE record_id BETWEEN 1710330 AND 1710500


Hope this helps.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5273
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: insert/update change one column

Postby bobm » Mon Feb 19, 2018 12:07 am

Thanks Robert

I'll give that a shot
Bob Mezzadri
EyeCOR By Nteon
bobm
 
Posts: 71
Joined: Thu Dec 29, 2011 8:24 pm


Return to SQL Databases

Who is online

Users browsing this forum: Bing [Bot] and 5 guests