PostgreSQL 10: Deleting Orphan Rows in Table A on only IDs existing in Table B?Optimising query on view that...
Why did the villain in the first Men in Black movie care about Earth's Cockroaches?
Which one of these password policies is more secure?
Can I write a book of my D&D game?
If I deleted a game I lost the disc for, can I reinstall it digitally?
Would a National Army of mercenaries be a feasible idea?
Writing a character who is going through a civilizing process without overdoing it?
Why is working on the same position for more than 15 years not a red flag?
Am I a Rude Number?
Are there any modern advantages of a fire piston?
Can I become debt free or should I file bankruptcy ? How to manage my debt and finances?
Can a hotel cancel a confirmed reservation?
Why do stocks necessarily drop during a recession?
Why are the books in the Game of Thrones citadel library shelved spine inwards?
It took me a lot of time to make this, pls like. (YouTube Comments #1)
Cookies - Should the toggles be on?
Dilemma of explaining to interviewer that he is the reason for declining second interview
Why Normality assumption in linear regression
A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?
What is the lore-based reason that the Spectator has the Create Food and Water trait, instead of simply not requiring food and water?
Why would the Pakistan airspace closure cancel flights not headed to Pakistan itself?
Porting Linux to another platform requirements
What is 6÷2×(1+2) =?
Eww, those bytes are gross
How to prevent cleaner from hanging my lock screen in Ubuntu 16.04
PostgreSQL 10: Deleting Orphan Rows in Table A on only IDs existing in Table B?
Optimising query on view that merges similar tables with a clear discriminatorStoring and querying rolling data in PostgreSQLBest way of finding rows referencing a given id on PostgreSQLDeleting aged postgresql rows with tricky conditionPostgreSQL Partitioned table update triggerLarge batch INSERTs / UPDATEs and DELETEs Performance problemPostgreSQL DELETE query hangs (and so does EXPLAIN ANALYZE!)Best way to flag a row for future deletion?How to add a PostgreSQL 10 identity column to an existing table with rows?Large update and delete based on another table
How do I delete orphan rows in Table A:
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | f | d | 2 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
On only IDs existing in Table B (only check IDs a & c, leave d alone):
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
Result(only the second row a,f,d,2 was deleted):
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
This is just an example, real tables contain many more IDs and variations.
My thinking is that I should group by ID on Table B to a temp table then loop delete on non matching rows on Table A per ID.
Because I'm new to PostgreSQL, can you show me how this can be done? Also, if there's a better way, please let me know as well. Thanks in advance!
postgresql postgresql-10
asked 4 mins ago
sojim2sojim2
101
New contributor
sojim2 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
How do I delete orphan rows in Table A:
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | f | d | 2 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
On only IDs existing in Table B (only check IDs a & c, leave d alone):
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
Result(only the second row a,f,d,2 was deleted):
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
This is just an example, real tables contain many more IDs and variations.
My thinking is that I should group by ID on Table B to a temp table then loop delete on non matching rows on Table A per ID.
Because I'm new to PostgreSQL, can you show me how this can be done? Also, if there's a better way, please let me know as well. Thanks in advance!
postgresql postgresql-10
asked 4 mins ago
sojim2sojim2
101
New contributor
sojim2 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
How do I delete orphan rows in Table A:
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | f | d | 2 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
On only IDs existing in Table B (only check IDs a & c, leave d alone):
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
Result(only the second row a,f,d,2 was deleted):
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
This is just an example, real tables contain many more IDs and variations.
My thinking is that I should group by ID on Table B to a temp table then loop delete on non matching rows on Table A per ID.
Because I'm new to PostgreSQL, can you show me how this can be done? Also, if there's a better way, please let me know as well. Thanks in advance!
postgresql postgresql-10
asked 4 mins ago
sojim2sojim2
101
New contributor
sojim2 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
How do I delete orphan rows in Table A:
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | f | d | 2 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
On only IDs existing in Table B (only check IDs a & c, leave d alone):
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
Result(only the second row a,f,d,2 was deleted):
+---------+--------+----------+-------+
| ID | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 3 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
This is just an example, real tables contain many more IDs and variations.
My thinking is that I should group by ID on Table B to a temp table then loop delete on non matching rows on Table A per ID.
Because I'm new to PostgreSQL, can you show me how this can be done? Also, if there's a better way, please let me know as well. Thanks in advance!
postgresql postgresql-10
postgresql postgresql-10
asked 4 mins ago
sojim2sojim2
101
New contributor
sojim2 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
sojim2sojim2
101
New contributor
sojim2 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
sojim2sojim2
101
New contributor
sojim2 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
sojim2sojim2
101
asked 4 mins ago
sojim2sojim2
101
101
New contributor
sojim2 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
sojim2 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
sojim2 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
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
});
}
});
sojim2 is a new contributor. Be nice, and check out our Code of Conduct.
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%2f231023%2fpostgresql-10-deleting-orphan-rows-in-table-a-on-only-ids-existing-in-table-b%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
sojim2 is a new contributor. Be nice, and check out our Code of Conduct.
sojim2 is a new contributor. Be nice, and check out our Code of Conduct.
sojim2 is a new contributor. Be nice, and check out our Code of Conduct.
sojim2 is a new contributor. Be nice, and check out our Code of Conduct.
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%2f231023%2fpostgresql-10-deleting-orphan-rows-in-table-a-on-only-ids-existing-in-table-b%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
