Multiple queries that calculate data and then updates
0
$begingroup$
How can I improve this code, which complies with the requirements of maintainability, performance and good practices?
Values that I am going to update:
UPDATE table
SET feactualizacion = timezone('America/New_York', CURRENT_TIMESTAMP),
valvalor = result.valvalor
from
At this point I am getting two values, from different filters, then I do a subtraction calculation, which is the data I need to update:
(select (select valvalor as promedio
from egma.tegma_contratos
WHERE (cdcategoria = 'Gen')
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP))) -
(select valvalor as promedio
from egma.tegma_contratos
WHERE (cdcategoria = 'Cont')
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND
fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP))) as valvalor) as result
Then at this point is where I apply the filter of the final value to update. This would be what I get from the previous query:
,egma.tegma_contratos
WHERE cdcategoria = 'Po rel'
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP));
sql postgresql
edited 7 mins ago
Jamal♦
30.3k11117227
asked 12 hours ago
Kmiilo Berrio MontoyaKmiilo Berrio Montoya
11
New contributor
Kmiilo Berrio Montoya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
|
show 2 more comments
0
$begingroup$
How can I improve this code, which complies with the requirements of maintainability, performance and good practices?
Values that I am going to update:
UPDATE table
SET feactualizacion = timezone('America/New_York', CURRENT_TIMESTAMP),
valvalor = result.valvalor
from
At this point I am getting two values, from different filters, then I do a subtraction calculation, which is the data I need to update:
(select (select valvalor as promedio
from egma.tegma_contratos
WHERE (cdcategoria = 'Gen')
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP))) -
(select valvalor as promedio
from egma.tegma_contratos
WHERE (cdcategoria = 'Cont')
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND
fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP))) as valvalor) as result
Then at this point is where I apply the filter of the final value to update. This would be what I get from the previous query:
,egma.tegma_contratos
WHERE cdcategoria = 'Po rel'
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP));
sql postgresql
edited 7 mins ago
Jamal♦
30.3k11117227
asked 12 hours ago
Kmiilo Berrio MontoyaKmiilo Berrio Montoya
11
New contributor
Kmiilo Berrio Montoya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
$begingroup$
The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
12 hours ago
$begingroup$
a bit better? @TobySpeight
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
2
$begingroup$
Not much - what's the purpose of the code? The question body could do with a lot more explanation of what we're querying, and why. It's very hard to review SQL without any knowledge of the tables involved, so you definitely want to describe them, too.
$endgroup$
– Toby Speight
11 hours ago
$begingroup$
the purpose of the code, is defined in the title, I want to have a way to write this code, with best practices and performance, I think the code is explained only, where you get from a nested query an output with data, which only I get a record and a column, and this is the reference in the set that is the column that I want to update
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
1
$begingroup$
"Calculate data and update" could describe almost any program or function. We need a bit of context to effectively review the code. What are the data you're retrieving, and what result is required? A code dump with no explanation isn't enough, but if you can provide that necessary explanation, then Code Review is indeed the place for you.
$endgroup$
– Toby Speight
11 hours ago
|
show 2 more comments
0
0
0
$begingroup$
How can I improve this code, which complies with the requirements of maintainability, performance and good practices?
Values that I am going to update:
UPDATE table
SET feactualizacion = timezone('America/New_York', CURRENT_TIMESTAMP),
valvalor = result.valvalor
from
At this point I am getting two values, from different filters, then I do a subtraction calculation, which is the data I need to update:
(select (select valvalor as promedio
from egma.tegma_contratos
WHERE (cdcategoria = 'Gen')
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP))) -
(select valvalor as promedio
from egma.tegma_contratos
WHERE (cdcategoria = 'Cont')
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND
fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP))) as valvalor) as result
Then at this point is where I apply the filter of the final value to update. This would be what I get from the previous query:
,egma.tegma_contratos
WHERE cdcategoria = 'Po rel'
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP));
sql postgresql
edited 7 mins ago
Jamal♦
30.3k11117227
asked 12 hours ago
Kmiilo Berrio MontoyaKmiilo Berrio Montoya
11
New contributor
Kmiilo Berrio Montoya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
How can I improve this code, which complies with the requirements of maintainability, performance and good practices?
Values that I am going to update:
UPDATE table
SET feactualizacion = timezone('America/New_York', CURRENT_TIMESTAMP),
valvalor = result.valvalor
from
At this point I am getting two values, from different filters, then I do a subtraction calculation, which is the data I need to update:
(select (select valvalor as promedio
from egma.tegma_contratos
WHERE (cdcategoria = 'Gen')
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP))) -
(select valvalor as promedio
from egma.tegma_contratos
WHERE (cdcategoria = 'Cont')
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND
fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP))) as valvalor) as result
Then at this point is where I apply the filter of the final value to update. This would be what I get from the previous query:
,egma.tegma_contratos
WHERE cdcategoria = 'Po rel'
AND fefuente = date_trunc('DAY', TIMEZONE('America/New_York', CURRENT_TIMESTAMP))
AND fehora = extract(hour from TIMEZONE('America/New_York', CURRENT_TIMESTAMP));
sql postgresql
sql postgresql
edited 7 mins ago
Jamal♦
30.3k11117227
asked 12 hours ago
Kmiilo Berrio MontoyaKmiilo Berrio Montoya
11
New contributor
Kmiilo Berrio Montoya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 7 mins ago
Jamal♦
30.3k11117227
asked 12 hours ago
Kmiilo Berrio MontoyaKmiilo Berrio Montoya
11
New contributor
Kmiilo Berrio Montoya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 7 mins ago
Jamal♦
30.3k11117227
edited 7 mins ago
Jamal♦
30.3k11117227
edited 7 mins ago
Jamal♦
30.3k11117227
30.3k11117227
asked 12 hours ago
Kmiilo Berrio MontoyaKmiilo Berrio Montoya
11
New contributor
Kmiilo Berrio Montoya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 12 hours ago
Kmiilo Berrio MontoyaKmiilo Berrio Montoya
11
asked 12 hours ago
Kmiilo Berrio MontoyaKmiilo Berrio Montoya
11
11
New contributor
Kmiilo Berrio Montoya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Kmiilo Berrio Montoya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Kmiilo Berrio Montoya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$begingroup$
The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
12 hours ago
$begingroup$
a bit better? @TobySpeight
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
2
$begingroup$
Not much - what's the purpose of the code? The question body could do with a lot more explanation of what we're querying, and why. It's very hard to review SQL without any knowledge of the tables involved, so you definitely want to describe them, too.
$endgroup$
– Toby Speight
11 hours ago
$begingroup$
the purpose of the code, is defined in the title, I want to have a way to write this code, with best practices and performance, I think the code is explained only, where you get from a nested query an output with data, which only I get a record and a column, and this is the reference in the set that is the column that I want to update
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
1
$begingroup$
"Calculate data and update" could describe almost any program or function. We need a bit of context to effectively review the code. What are the data you're retrieving, and what result is required? A code dump with no explanation isn't enough, but if you can provide that necessary explanation, then Code Review is indeed the place for you.
$endgroup$
– Toby Speight
11 hours ago
|
show 2 more comments
$begingroup$
The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
12 hours ago
$begingroup$
a bit better? @TobySpeight
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
2
$begingroup$
Not much - what's the purpose of the code? The question body could do with a lot more explanation of what we're querying, and why. It's very hard to review SQL without any knowledge of the tables involved, so you definitely want to describe them, too.
$endgroup$
– Toby Speight
11 hours ago
$begingroup$
the purpose of the code, is defined in the title, I want to have a way to write this code, with best practices and performance, I think the code is explained only, where you get from a nested query an output with data, which only I get a record and a column, and this is the reference in the set that is the column that I want to update
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
1
$begingroup$
"Calculate data and update" could describe almost any program or function. We need a bit of context to effectively review the code. What are the data you're retrieving, and what result is required? A code dump with no explanation isn't enough, but if you can provide that necessary explanation, then Code Review is indeed the place for you.
$endgroup$
– Toby Speight
11 hours ago
$begingroup$
The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
12 hours ago
$begingroup$
The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
12 hours ago
$begingroup$
a bit better? @TobySpeight
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
$begingroup$
a bit better? @TobySpeight
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
2
2
$begingroup$
Not much - what's the purpose of the code? The question body could do with a lot more explanation of what we're querying, and why. It's very hard to review SQL without any knowledge of the tables involved, so you definitely want to describe them, too.
$endgroup$
– Toby Speight
11 hours ago
$begingroup$
Not much - what's the purpose of the code? The question body could do with a lot more explanation of what we're querying, and why. It's very hard to review SQL without any knowledge of the tables involved, so you definitely want to describe them, too.
$endgroup$
– Toby Speight
11 hours ago
$begingroup$
the purpose of the code, is defined in the title, I want to have a way to write this code, with best practices and performance, I think the code is explained only, where you get from a nested query an output with data, which only I get a record and a column, and this is the reference in the set that is the column that I want to update
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
$begingroup$
the purpose of the code, is defined in the title, I want to have a way to write this code, with best practices and performance, I think the code is explained only, where you get from a nested query an output with data, which only I get a record and a column, and this is the reference in the set that is the column that I want to update
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
1
1
$begingroup$
"Calculate data and update" could describe almost any program or function. We need a bit of context to effectively review the code. What are the data you're retrieving, and what result is required? A code dump with no explanation isn't enough, but if you can provide that necessary explanation, then Code Review is indeed the place for you.
$endgroup$
– Toby Speight
11 hours ago
$begingroup$
"Calculate data and update" could describe almost any program or function. We need a bit of context to effectively review the code. What are the data you're retrieving, and what result is required? A code dump with no explanation isn't enough, but if you can provide that necessary explanation, then Code Review is indeed the place for you.
$endgroup$
– Toby Speight
11 hours ago
|
show 2 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
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: "196"
};
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
});
}
});
Kmiilo Berrio Montoya is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
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%2fcodereview.stackexchange.com%2fquestions%2f212558%2fmultiple-queries-that-calculate-data-and-then-updates%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
Kmiilo Berrio Montoya is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Kmiilo Berrio Montoya is a new contributor. Be nice, and check out our Code of Conduct.
Kmiilo Berrio Montoya is a new contributor. Be nice, and check out our Code of Conduct.
Kmiilo Berrio Montoya is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Code Review 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.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
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%2fcodereview.stackexchange.com%2fquestions%2f212558%2fmultiple-queries-that-calculate-data-and-then-updates%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
$begingroup$
The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles.
$endgroup$
– Toby Speight
12 hours ago
$begingroup$
a bit better? @TobySpeight
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
2
$begingroup$
Not much - what's the purpose of the code? The question body could do with a lot more explanation of what we're querying, and why. It's very hard to review SQL without any knowledge of the tables involved, so you definitely want to describe them, too.
$endgroup$
– Toby Speight
11 hours ago
$begingroup$
the purpose of the code, is defined in the title, I want to have a way to write this code, with best practices and performance, I think the code is explained only, where you get from a nested query an output with data, which only I get a record and a column, and this is the reference in the set that is the column that I want to update
$endgroup$
– Kmiilo Berrio Montoya
11 hours ago
1
$begingroup$
"Calculate data and update" could describe almost any program or function. We need a bit of context to effectively review the code. What are the data you're retrieving, and what result is required? A code dump with no explanation isn't enough, but if you can provide that necessary explanation, then Code Review is indeed the place for you.
$endgroup$
– Toby Speight
11 hours ago