I have a table called map_tags
:
map_id | map_license | map_desc
And another table (widgets
) whose records contains a foreign key reference (1 to 1) to a map_tags
record:
widget_id | map_id | widget_name
Given the constraint that all map_license
s are unique (however are not set up as keys on map_tags
), then if I have a map_license
and a widget_name
, I'd like to perform an insert on widgets
all inside of the same SQL statement:
INSERT INTO
widgets w
(
map_id,
widget_name
)
VALUES (
(
SELECT
mt.map_id
FROM
map_tags mt
WHERE
// This should work and return a single record because map_license is unique
mt.map_license = '12345'
),
'Bupo'
)
I believe I'm on the right track but know right off the bat that this is incorrect SQL for Postgres. Does anybody know the proper way to achieve such a single query?
INSERT INTO widgets
(
map_id,
widget_name
)
SELECT
mt.map_id, 'Bupo'
FROM
map_tags mt
WHERE
mt.map_license = '12345'