INSERTING a single field into a table, referenced from another table

Question

I need to insert a field in a that references an id field in another table. The id field it is to going is next to the field 'test' (column - codedescription, table typecategory) and coming from an id field next to the word 'assessment' (column categorydescription, table typecategory)

INSERT INTO codetype
(typecategoryid)
Where codedescription='test'
SELECT id FROM typecategory WHERE categorydescription='Assessment Types'

There are plenty of examples of inserting entire columns but nobody has written how to insert a single field from another table.

table - codetype id bigserial primary key codedescription varchar
typecategoryid bigint foreign key to typecatogory on the ID column

Table - typecategory ID big serial primary key categorydescription varchar

Which RDBMS are you using? What happens when you run this script? Are you getting an error, or just not the results you expect? — CLAbeel, Nov 07 '16 at 18:10
Just to be clear, you are trying to create the `typecategoryid` field at the same time you are populating it? — CLAbeel, Nov 07 '16 at 18:12
I am using postgres. There is a column typecategoryid with plenty of info. I need to populate the last three fields with an id field from a different table (typecategory) — Jeff Vance, Nov 07 '16 at 18:16

score 0 · Answer 1 · edited May 23 '17 at 12:13

0

If the column already exists and there are are already records in the rest of the columns in the table, then you need an UPDATE statement, not an INSERT.

Looks like this post might help you: Update a column of a table with a column of another table in PostgreSQL

maybe

UPDATE codetype c
SET    c.typecategoryid = t.id 
FROM   typecategory t
WHERE  c.codedescription = 'test' and t.categorydescription='Assessment Types'

edited May 23 '17 at 12:13

Community

1
1

answered Nov 07 '16 at 18:10

CLAbeel

1,078
14
20

UPDATE codetype (typecategoryid) WHERE codedescription='Test' SET typecategor.id = codetype.typecategoryid WHERE typecategoryid=4 ??? – Jeff Vance Nov 07 '16 at 18:30
I appreciate your help. Thank you very much. – Jeff Vance Nov 15 '16 at 12:39

INSERTING a single field into a table, referenced from another table

1 Answers1