How do I insert a record and put the ID into an existing record in another table?Postgresql Retrieve / Find...
How can I handle players killing my NPC outside of combat?
Would water spill from a bowl in a Bag of Holding?
Modern Algebraic Geometry and Analytic Number Theory
How can find the 2D Voronoi cell area distribution?
How unreachable are Jupiter's moons from Mars with the technology developed for going to Mars?
I have trouble understanding this fallacy: "If A, then B. Therefore if not-B, then not-A."
Is the symmetric product of an abelian variety a CY variety?
If angels and devils are the same species, why would their mortal offspring appear physically different?
Why did Ylvis use "go" instead of "say" in phrases like "Dog goes 'woof'"?
Was there a pre-determined arrangement for the division of Germany in case it surrendered before any Soviet forces entered its territory?
Is there any danger of my neighbor having my wife's signature?
How bad is a Computer Science course that doesn't teach Design Patterns?
Is practicing on a digital piano harmful to an experienced piano player?
Sensor logger for Raspberry Pi in a stratospheric probe
Equivalent of "illegal" for violating civil law
How can I automatically launch GPSD on startup?
What are some idioms that means something along the lines of "switching it up every day to not do the same thing over and over"?
What could cause an entire planet of humans to become aphasic?
Fraction within another fraction
No option to ask a question in https://developer.salesforce.com discussion forums
my cron command doesn’t work
Specific list manipulation
How can I prevent an oracle who can see into the past from knowing everything that has happened?
How to deal with an underperforming subordinate?
How do I insert a record and put the ID into an existing record in another table?
Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logsPossible to have nested inserts in Postgres 8.4?How do I go about easy table input without creating an application?Most efficient way to do many UPDATES in PostgresqlHow to return a record from function, executed by INSERT/UPDATE rule (trigger)?Select Distinct on subset of columns, name different set of columns to returnPostgreSQL function to insert to table from another table has different structureInserting in PostgreSQL Foreign Table Violates Primary Key Constraint10Advance primary key after mass insertJoining to same table and updating column on row match for multiple columns
I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy.
Simplistic example:
create table aaa (id serial primary key, foo int);
create table foos (id serial primary key, foo int, aaa_id int);
Imagine foos has rows which I want to insert in aaa. But I want a record of aaa.id stored in foos.aaa_id.
Heres some code that definitely does not work:
with rows as (
insert into aaa (foo)
select foo from foos
returning foos.id as foos_id, aaa.id as aaa_id
)
update foos
set aaa_id = (select aaa_id from rows where foos_id = foos.id);
I can't find a way of getting the "returning" clause to return anything other than records from the table it inserted.
Surely there must be a simple way to do this?
postgresql
asked Jul 8 '16 at 11:18
Andy JonesAndy Jones
1084
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy.
Simplistic example:
create table aaa (id serial primary key, foo int);
create table foos (id serial primary key, foo int, aaa_id int);
Imagine foos has rows which I want to insert in aaa. But I want a record of aaa.id stored in foos.aaa_id.
Heres some code that definitely does not work:
with rows as (
insert into aaa (foo)
select foo from foos
returning foos.id as foos_id, aaa.id as aaa_id
)
update foos
set aaa_id = (select aaa_id from rows where foos_id = foos.id);
I can't find a way of getting the "returning" clause to return anything other than records from the table it inserted.
Surely there must be a simple way to do this?
postgresql
asked Jul 8 '16 at 11:18
Andy JonesAndy Jones
1084
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
MaybeUPDATE foos SET aaa_id = (INSERT INTO aaa .... RETURNING id);?
– Adam
Jul 8 '16 at 18:58
@Adam - that would be lovely. But you can only use SELECT there, not INSERT...
– Andy Jones
Jul 11 '16 at 9:06
Are you using postgresql < 9.1? See those questions and answers: stackoverflow.com/questions/7191902/…, stackoverflow.com/questions/3206951/….
– Adam
Jul 11 '16 at 9:16
@Adam, I'm using 9.5. It doesn't work, and the docs for 9.5 are quite clear about it. "column_name = SUB SELECT" right there in the synopsis. I wish it were otherwise.
– Andy Jones
Jul 11 '16 at 10:29
add a comment |
I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy.
Simplistic example:
create table aaa (id serial primary key, foo int);
create table foos (id serial primary key, foo int, aaa_id int);
Imagine foos has rows which I want to insert in aaa. But I want a record of aaa.id stored in foos.aaa_id.
Heres some code that definitely does not work:
with rows as (
insert into aaa (foo)
select foo from foos
returning foos.id as foos_id, aaa.id as aaa_id
)
update foos
set aaa_id = (select aaa_id from rows where foos_id = foos.id);
I can't find a way of getting the "returning" clause to return anything other than records from the table it inserted.
Surely there must be a simple way to do this?
postgresql
asked Jul 8 '16 at 11:18
Andy JonesAndy Jones
1084
I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy.
Simplistic example:
create table aaa (id serial primary key, foo int);
create table foos (id serial primary key, foo int, aaa_id int);
Imagine foos has rows which I want to insert in aaa. But I want a record of aaa.id stored in foos.aaa_id.
Heres some code that definitely does not work:
with rows as (
insert into aaa (foo)
select foo from foos
returning foos.id as foos_id, aaa.id as aaa_id
)
update foos
set aaa_id = (select aaa_id from rows where foos_id = foos.id);
I can't find a way of getting the "returning" clause to return anything other than records from the table it inserted.
Surely there must be a simple way to do this?
postgresql
postgresql
asked Jul 8 '16 at 11:18
Andy JonesAndy Jones
1084
asked Jul 8 '16 at 11:18
Andy JonesAndy Jones
1084
asked Jul 8 '16 at 11:18
Andy JonesAndy Jones
1084
asked Jul 8 '16 at 11:18
Andy JonesAndy Jones
1084
asked Jul 8 '16 at 11:18
Andy JonesAndy Jones
1084
1084
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
MaybeUPDATE foos SET aaa_id = (INSERT INTO aaa .... RETURNING id);?
– Adam
Jul 8 '16 at 18:58
@Adam - that would be lovely. But you can only use SELECT there, not INSERT...
– Andy Jones
Jul 11 '16 at 9:06
Are you using postgresql < 9.1? See those questions and answers: stackoverflow.com/questions/7191902/…, stackoverflow.com/questions/3206951/….
– Adam
Jul 11 '16 at 9:16
@Adam, I'm using 9.5. It doesn't work, and the docs for 9.5 are quite clear about it. "column_name = SUB SELECT" right there in the synopsis. I wish it were otherwise.
– Andy Jones
Jul 11 '16 at 10:29
add a comment |
MaybeUPDATE foos SET aaa_id = (INSERT INTO aaa .... RETURNING id);?
– Adam
Jul 8 '16 at 18:58
@Adam - that would be lovely. But you can only use SELECT there, not INSERT...
– Andy Jones
Jul 11 '16 at 9:06
Are you using postgresql < 9.1? See those questions and answers: stackoverflow.com/questions/7191902/…, stackoverflow.com/questions/3206951/….
– Adam
Jul 11 '16 at 9:16
@Adam, I'm using 9.5. It doesn't work, and the docs for 9.5 are quite clear about it. "column_name = SUB SELECT" right there in the synopsis. I wish it were otherwise.
– Andy Jones
Jul 11 '16 at 10:29
Maybe
UPDATE foos SET aaa_id = (INSERT INTO aaa .... RETURNING id);?– Adam
Jul 8 '16 at 18:58
Maybe
UPDATE foos SET aaa_id = (INSERT INTO aaa .... RETURNING id);?– Adam
Jul 8 '16 at 18:58
@Adam - that would be lovely. But you can only use SELECT there, not INSERT...
– Andy Jones
Jul 11 '16 at 9:06
@Adam - that would be lovely. But you can only use SELECT there, not INSERT...
– Andy Jones
Jul 11 '16 at 9:06
Are you using postgresql < 9.1? See those questions and answers: stackoverflow.com/questions/7191902/…, stackoverflow.com/questions/3206951/….
– Adam
Jul 11 '16 at 9:16
Are you using postgresql < 9.1? See those questions and answers: stackoverflow.com/questions/7191902/…, stackoverflow.com/questions/3206951/….
– Adam
Jul 11 '16 at 9:16
@Adam, I'm using 9.5. It doesn't work, and the docs for 9.5 are quite clear about it. "column_name = SUB SELECT" right there in the synopsis. I wish it were otherwise.
– Andy Jones
Jul 11 '16 at 10:29
@Adam, I'm using 9.5. It doesn't work, and the docs for 9.5 are quite clear about it. "column_name = SUB SELECT" right there in the synopsis. I wish it were otherwise.
– Andy Jones
Jul 11 '16 at 10:29
add a comment |
1 Answer
1
active
oldest
votes
You can use DO block:
DO $$
DECLARE
rows CURSOR FOR
SELECT * FROM foos;
BEGIN
FOR r IN rows LOOP
WITH _ins AS (
INSERT INTO aaa (foo)
VALUES (r.foo)
RETURNING aaa.id AS aaa_id
)
UPDATE foos
SET aaa_id = (SELECT aaa_id FROM _ins)
WHERE id = r.id;
END LOOP;
END$$;
answered Jul 9 '16 at 10:04
Dima PavlovDima Pavlov
1
Sorry, I should have made it clear that I didn't want to use cursors. I could be doing this in more expressive langages if I wanted to deal with each record one at a time...
– Andy Jones
Jul 11 '16 at 9:07
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f143325%2fhow-do-i-insert-a-record-and-put-the-id-into-an-existing-record-in-another-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use DO block:
DO $$
DECLARE
rows CURSOR FOR
SELECT * FROM foos;
BEGIN
FOR r IN rows LOOP
WITH _ins AS (
INSERT INTO aaa (foo)
VALUES (r.foo)
RETURNING aaa.id AS aaa_id
)
UPDATE foos
SET aaa_id = (SELECT aaa_id FROM _ins)
WHERE id = r.id;
END LOOP;
END$$;
answered Jul 9 '16 at 10:04
Dima PavlovDima Pavlov
1
Sorry, I should have made it clear that I didn't want to use cursors. I could be doing this in more expressive langages if I wanted to deal with each record one at a time...
– Andy Jones
Jul 11 '16 at 9:07
add a comment |
You can use DO block:
DO $$
DECLARE
rows CURSOR FOR
SELECT * FROM foos;
BEGIN
FOR r IN rows LOOP
WITH _ins AS (
INSERT INTO aaa (foo)
VALUES (r.foo)
RETURNING aaa.id AS aaa_id
)
UPDATE foos
SET aaa_id = (SELECT aaa_id FROM _ins)
WHERE id = r.id;
END LOOP;
END$$;
answered Jul 9 '16 at 10:04
Dima PavlovDima Pavlov
1
Sorry, I should have made it clear that I didn't want to use cursors. I could be doing this in more expressive langages if I wanted to deal with each record one at a time...
– Andy Jones
Jul 11 '16 at 9:07
add a comment |
You can use DO block:
DO $$
DECLARE
rows CURSOR FOR
SELECT * FROM foos;
BEGIN
FOR r IN rows LOOP
WITH _ins AS (
INSERT INTO aaa (foo)
VALUES (r.foo)
RETURNING aaa.id AS aaa_id
)
UPDATE foos
SET aaa_id = (SELECT aaa_id FROM _ins)
WHERE id = r.id;
END LOOP;
END$$;
answered Jul 9 '16 at 10:04
Dima PavlovDima Pavlov
1
You can use DO block:
DO $$
DECLARE
rows CURSOR FOR
SELECT * FROM foos;
BEGIN
FOR r IN rows LOOP
WITH _ins AS (
INSERT INTO aaa (foo)
VALUES (r.foo)
RETURNING aaa.id AS aaa_id
)
UPDATE foos
SET aaa_id = (SELECT aaa_id FROM _ins)
WHERE id = r.id;
END LOOP;
END$$;
answered Jul 9 '16 at 10:04
Dima PavlovDima Pavlov
1
answered Jul 9 '16 at 10:04
Dima PavlovDima Pavlov
1
answered Jul 9 '16 at 10:04
Dima PavlovDima Pavlov
1
answered Jul 9 '16 at 10:04
Dima PavlovDima Pavlov
1
1
Sorry, I should have made it clear that I didn't want to use cursors. I could be doing this in more expressive langages if I wanted to deal with each record one at a time...
– Andy Jones
Jul 11 '16 at 9:07
add a comment |
Sorry, I should have made it clear that I didn't want to use cursors. I could be doing this in more expressive langages if I wanted to deal with each record one at a time...
– Andy Jones
Jul 11 '16 at 9:07
Sorry, I should have made it clear that I didn't want to use cursors. I could be doing this in more expressive langages if I wanted to deal with each record one at a time...
– Andy Jones
Jul 11 '16 at 9:07
Sorry, I should have made it clear that I didn't want to use cursors. I could be doing this in more expressive langages if I wanted to deal with each record one at a time...
– Andy Jones
Jul 11 '16 at 9:07
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f143325%2fhow-do-i-insert-a-record-and-put-the-id-into-an-existing-record-in-another-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Maybe
UPDATE foos SET aaa_id = (INSERT INTO aaa .... RETURNING id);?– Adam
Jul 8 '16 at 18:58
@Adam - that would be lovely. But you can only use SELECT there, not INSERT...
– Andy Jones
Jul 11 '16 at 9:06
Are you using postgresql < 9.1? See those questions and answers: stackoverflow.com/questions/7191902/…, stackoverflow.com/questions/3206951/….
– Adam
Jul 11 '16 at 9:16
@Adam, I'm using 9.5. It doesn't work, and the docs for 9.5 are quite clear about it. "column_name = SUB SELECT" right there in the synopsis. I wish it were otherwise.
– Andy Jones
Jul 11 '16 at 10:29