help with inserting into the same table and changing a field

Questions and answers regarding general SQL and backend databases

help with inserting into the same table and changing a field

Postby bobm » Fri Sep 23, 2016 4:31 pm

I am trying to update a table by copying existing rows and change one field in the new row but getting various errors when doing so. The latest is:
ERROR: column "diagnosis_id" is of type integer but expression is of type boolean
LINE 2: ...region_char_code, procedure_id, hide, code_type, (diagnosis_...
^
HINT: You will need to rewrite or cast the expression.

Code: Select all
INSERT INTO diagnosis_procedures (flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id)
SELECT flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, (diagnosis_id = '14742')
FROM diagnosis_procedures
WHERE diagnosis_id = '13783'


Any help would be much appreciated. I am using postgresql for my database.
Thank you,
Bob
Bob Mezzadri
EyeCOR By Nteon
bobm
 
Posts: 67
Joined: Thu Dec 29, 2011 8:24 pm

Re: help with inserting into the same table and changing a f

Postby Bernd.N » Fri Sep 23, 2016 5:53 pm

It is as postgres says, your last field diagnosis_id is of type integer, but the expression below results into a boolean, because of the brackets around the expression: (diagnosis_id = '14742').
That evaluates into a true or false.
In case you want to put the diagnosis_id 14742 into the new record(s), just write
14742 AS diagnosis_id
That should do it.

I guess that your WHERE clause will also complain, when the diagnosis_id is really an integer, because then the 13783 may not have quotation marks ( ' ) around it.
Tip: In those cases, I always align the fields in the first INSERT INTO line to the fields in the second line (with blanks), so that the field pairs can be easily seen and checked.
And I think you do not need the AS diagnosis_id, just try.
Code: Select all
INSERT INTO diagnosis_procedures
      (flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id)
SELECT flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, 14742
FROM diagnosis_procedures
WHERE diagnosis_id = 13783
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 480
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: help with inserting into the same table and changing a f

Postby bobm » Fri Sep 23, 2016 6:24 pm

Thank you Bernd,
This is getting me closer. I guess something I forgot to mention is I have a primary key field set to not-null -
so when I run this I am getting the following message:

ERROR: null value in column "diagnosis_procedure_id" violates not-null constraint

How can I set the diagnosis_procedure_id to auto increment as new records are added?
Thanks again,
Bob
Bob Mezzadri
EyeCOR By Nteon
bobm
 
Posts: 67
Joined: Thu Dec 29, 2011 8:24 pm

Primary Key Creation when Inserting

Postby Bernd.N » Fri Sep 23, 2016 7:30 pm

That depends on the data type of your primary key.
We use for example only UUIDs, and we would have to create a new UUID with the expression
UPPER(uuid_generate_v4()::varchar)

So in case you use UUIDs, that would be

Code: Select all
INSERT INTO diagnosis_procedures
      (flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id, diagnosis_procedure_id)
SELECT flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, 14742,        UPPER(uuid_generate_v4()::varchar)
FROM diagnosis_procedures
WHERE diagnosis_id = 13783


In case you use integers as primary keys, I do not know at the moment how to do that in such an INSERT case, but I guess other users here can easily answer that.
You can also google with a smart word combination, like "postgres insert primary key autoincrement", I learned a lot with that strategy.
You will find for example
http://dba.stackexchange.com/questions/65662/postgres-how-to-insert-row-with-autoincrement-id

Servoy is using the same strategy for integers as far as I know, when you have autoincrement switched on for the table and Servoy adds a record.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 480
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: help with inserting into the same table and changing a f

Postby bobm » Fri Sep 23, 2016 8:09 pm

Thanks Bernd,

Yes, my primary key is an integer. I'll google this as well and see if I can figure it out from there. But if anyone has suggestions, please let me know.

Thanks,
Bob
Bob Mezzadri
EyeCOR By Nteon
bobm
 
Posts: 67
Joined: Thu Dec 29, 2011 8:24 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 3 guests