Store result of Select query to use later in sql file PSQL
I have PostgreSQL database. I run a sql file using PSQL command:
psql --dbname=postgresql://postgres:password@127.0.0.1:port/dbname < filename.sql
My sql file looks like:
delete from records where id = 1;
delete from recordsinfo where recordsinfoid IN (select recordsinfoid from records where id = 1);
But I have a foreign key dependency:
'records' FOREIGN KEY (recordsinfoid) REFERENCES
recordsinfo(recordsinfoid)
So I cannot have the subquery in the second delete statement as the records would have got deleted in the first delete statement.
Also I cannot put the second delete statement first because it would cause foreign key violation.
How do I delete all entries in recordsinfo corresponding to the subquery 'select recordsinfoid from records where id = 1' ?
sql postgresql psql postgresql-9.6
asked Dec 31 '18 at 8:15
user5155835user5155835
429521
add a comment |
I have PostgreSQL database. I run a sql file using PSQL command:
psql --dbname=postgresql://postgres:password@127.0.0.1:port/dbname < filename.sql
My sql file looks like:
delete from records where id = 1;
delete from recordsinfo where recordsinfoid IN (select recordsinfoid from records where id = 1);
But I have a foreign key dependency:
'records' FOREIGN KEY (recordsinfoid) REFERENCES
recordsinfo(recordsinfoid)
So I cannot have the subquery in the second delete statement as the records would have got deleted in the first delete statement.
Also I cannot put the second delete statement first because it would cause foreign key violation.
How do I delete all entries in recordsinfo corresponding to the subquery 'select recordsinfoid from records where id = 1' ?
sql postgresql psql postgresql-9.6
asked Dec 31 '18 at 8:15
user5155835user5155835
429521
add a comment |
I have PostgreSQL database. I run a sql file using PSQL command:
psql --dbname=postgresql://postgres:password@127.0.0.1:port/dbname < filename.sql
My sql file looks like:
delete from records where id = 1;
delete from recordsinfo where recordsinfoid IN (select recordsinfoid from records where id = 1);
But I have a foreign key dependency:
'records' FOREIGN KEY (recordsinfoid) REFERENCES
recordsinfo(recordsinfoid)
So I cannot have the subquery in the second delete statement as the records would have got deleted in the first delete statement.
Also I cannot put the second delete statement first because it would cause foreign key violation.
How do I delete all entries in recordsinfo corresponding to the subquery 'select recordsinfoid from records where id = 1' ?
sql postgresql psql postgresql-9.6
asked Dec 31 '18 at 8:15
user5155835user5155835
429521
I have PostgreSQL database. I run a sql file using PSQL command:
psql --dbname=postgresql://postgres:password@127.0.0.1:port/dbname < filename.sql
My sql file looks like:
delete from records where id = 1;
delete from recordsinfo where recordsinfoid IN (select recordsinfoid from records where id = 1);
But I have a foreign key dependency:
'records' FOREIGN KEY (recordsinfoid) REFERENCES
recordsinfo(recordsinfoid)
So I cannot have the subquery in the second delete statement as the records would have got deleted in the first delete statement.
Also I cannot put the second delete statement first because it would cause foreign key violation.
How do I delete all entries in recordsinfo corresponding to the subquery 'select recordsinfoid from records where id = 1' ?
sql postgresql psql postgresql-9.6
sql postgresql psql postgresql-9.6
asked Dec 31 '18 at 8:15
user5155835user5155835
429521
asked Dec 31 '18 at 8:15
user5155835user5155835
429521
asked Dec 31 '18 at 8:15
user5155835user5155835
429521
asked Dec 31 '18 at 8:15
user5155835user5155835
429521
asked Dec 31 '18 at 8:15
user5155835user5155835
429521
429521
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
Use temporary table as a record buffer:
create temp table tmp_records
as
select recordsinfoid from records where id = 1;
delete from records where id = 1;
delete from recordsinfo where recordsinfoid IN (select recordsinfoid from tmp_records);
answered Dec 31 '18 at 9:29
Petro KPetro K
1317
add a comment |
Use a data-modifying CTE:
with d as (
delete from records
where id = 1
returning *
)
delete from recordsinfo
where recordsinfoid in (select recordsinfoid from d);
answered Dec 31 '18 at 12:02
Gordon LinoffGordon Linoff
771k35304406
add a comment |
Use cursor to fetch the recordsinfoid from records.
make a string by appending recordsinfoid by seperating ','.
make delete query using the created string,
execute the query using exec()
I am using mssql .
answered Dec 31 '18 at 10:55
Nikhil V SNikhil V S
63
add a comment |
I solved it using:
DO $$
DECLARE
recordsinfoids INTEGER;
i INTEGER;
BEGIN
recordsinfoids := ARRAY(select recordsinfoid from records where id = 1);
delete from records where id = 1;
FOREACH i IN ARRAY recordsinfoids
LOOP
delete from recordsinfo where recordsinfoid = i;
END LOOP;
END $$;
Basically prefetch the values in an array, and then later delete
answered Dec 31 '18 at 14:57
user5155835user5155835
429521
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53985130%2fstore-result-of-select-query-to-use-later-in-sql-file-psql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use temporary table as a record buffer:
create temp table tmp_records
as
select recordsinfoid from records where id = 1;
delete from records where id = 1;
delete from recordsinfo where recordsinfoid IN (select recordsinfoid from tmp_records);
answered Dec 31 '18 at 9:29
Petro KPetro K
1317
add a comment |
Use temporary table as a record buffer:
create temp table tmp_records
as
select recordsinfoid from records where id = 1;
delete from records where id = 1;
delete from recordsinfo where recordsinfoid IN (select recordsinfoid from tmp_records);
answered Dec 31 '18 at 9:29
Petro KPetro K
1317
add a comment |
Use temporary table as a record buffer:
create temp table tmp_records
as
select recordsinfoid from records where id = 1;
delete from records where id = 1;
delete from recordsinfo where recordsinfoid IN (select recordsinfoid from tmp_records);
answered Dec 31 '18 at 9:29
Petro KPetro K
1317
Use temporary table as a record buffer:
create temp table tmp_records
as
select recordsinfoid from records where id = 1;
delete from records where id = 1;
delete from recordsinfo where recordsinfoid IN (select recordsinfoid from tmp_records);
answered Dec 31 '18 at 9:29
Petro KPetro K
1317
answered Dec 31 '18 at 9:29
Petro KPetro K
1317
answered Dec 31 '18 at 9:29
Petro KPetro K
1317
answered Dec 31 '18 at 9:29
Petro KPetro K
1317
1317
add a comment |
add a comment |
Use a data-modifying CTE:
with d as (
delete from records
where id = 1
returning *
)
delete from recordsinfo
where recordsinfoid in (select recordsinfoid from d);
answered Dec 31 '18 at 12:02
Gordon LinoffGordon Linoff
771k35304406
add a comment |
Use a data-modifying CTE:
with d as (
delete from records
where id = 1
returning *
)
delete from recordsinfo
where recordsinfoid in (select recordsinfoid from d);
answered Dec 31 '18 at 12:02
Gordon LinoffGordon Linoff
771k35304406
add a comment |
Use a data-modifying CTE:
with d as (
delete from records
where id = 1
returning *
)
delete from recordsinfo
where recordsinfoid in (select recordsinfoid from d);
answered Dec 31 '18 at 12:02
Gordon LinoffGordon Linoff
771k35304406
Use a data-modifying CTE:
with d as (
delete from records
where id = 1
returning *
)
delete from recordsinfo
where recordsinfoid in (select recordsinfoid from d);
answered Dec 31 '18 at 12:02
Gordon LinoffGordon Linoff
771k35304406
answered Dec 31 '18 at 12:02
Gordon LinoffGordon Linoff
771k35304406
answered Dec 31 '18 at 12:02
Gordon LinoffGordon Linoff
771k35304406
answered Dec 31 '18 at 12:02
Gordon LinoffGordon Linoff
771k35304406
771k35304406
add a comment |
add a comment |
Use cursor to fetch the recordsinfoid from records.
make a string by appending recordsinfoid by seperating ','.
make delete query using the created string,
execute the query using exec()
I am using mssql .
answered Dec 31 '18 at 10:55
Nikhil V SNikhil V S
63
add a comment |
Use cursor to fetch the recordsinfoid from records.
make a string by appending recordsinfoid by seperating ','.
make delete query using the created string,
execute the query using exec()
I am using mssql .
answered Dec 31 '18 at 10:55
Nikhil V SNikhil V S
63
add a comment |
Use cursor to fetch the recordsinfoid from records.
make a string by appending recordsinfoid by seperating ','.
make delete query using the created string,
execute the query using exec()
I am using mssql .
answered Dec 31 '18 at 10:55
Nikhil V SNikhil V S
63
Use cursor to fetch the recordsinfoid from records.
make a string by appending recordsinfoid by seperating ','.
make delete query using the created string,
execute the query using exec()
I am using mssql .
answered Dec 31 '18 at 10:55
Nikhil V SNikhil V S
63
answered Dec 31 '18 at 10:55
Nikhil V SNikhil V S
63
answered Dec 31 '18 at 10:55
Nikhil V SNikhil V S
63
answered Dec 31 '18 at 10:55
Nikhil V SNikhil V S
63
63
add a comment |
add a comment |
I solved it using:
DO $$
DECLARE
recordsinfoids INTEGER;
i INTEGER;
BEGIN
recordsinfoids := ARRAY(select recordsinfoid from records where id = 1);
delete from records where id = 1;
FOREACH i IN ARRAY recordsinfoids
LOOP
delete from recordsinfo where recordsinfoid = i;
END LOOP;
END $$;
Basically prefetch the values in an array, and then later delete
answered Dec 31 '18 at 14:57
user5155835user5155835
429521
add a comment |
I solved it using:
DO $$
DECLARE
recordsinfoids INTEGER;
i INTEGER;
BEGIN
recordsinfoids := ARRAY(select recordsinfoid from records where id = 1);
delete from records where id = 1;
FOREACH i IN ARRAY recordsinfoids
LOOP
delete from recordsinfo where recordsinfoid = i;
END LOOP;
END $$;
Basically prefetch the values in an array, and then later delete
answered Dec 31 '18 at 14:57
user5155835user5155835
429521
add a comment |
I solved it using:
DO $$
DECLARE
recordsinfoids INTEGER;
i INTEGER;
BEGIN
recordsinfoids := ARRAY(select recordsinfoid from records where id = 1);
delete from records where id = 1;
FOREACH i IN ARRAY recordsinfoids
LOOP
delete from recordsinfo where recordsinfoid = i;
END LOOP;
END $$;
Basically prefetch the values in an array, and then later delete
answered Dec 31 '18 at 14:57
user5155835user5155835
429521
I solved it using:
DO $$
DECLARE
recordsinfoids INTEGER;
i INTEGER;
BEGIN
recordsinfoids := ARRAY(select recordsinfoid from records where id = 1);
delete from records where id = 1;
FOREACH i IN ARRAY recordsinfoids
LOOP
delete from recordsinfo where recordsinfoid = i;
END LOOP;
END $$;
Basically prefetch the values in an array, and then later delete
answered Dec 31 '18 at 14:57
user5155835user5155835
429521
edited Jan 2 at 16:53
answered Dec 31 '18 at 14:57
user5155835user5155835
429521
answered Dec 31 '18 at 14:57
user5155835user5155835
429521
answered Dec 31 '18 at 14:57
user5155835user5155835
429521
429521
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53985130%2fstore-result-of-select-query-to-use-later-in-sql-file-psql%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
