In SQL Stuck trying to UPDATE a table column from the results of a COUNT
I am building up a table for statistical analysis of production data. I have extracted all the distinct cases, and constructed a count to find how many times each unique case appears in the table.
My issue comes when trying to UPDATE the table's "Number" column with the results from the COUNT
Here is the code I have so far.
TRUNCATE TABLE WeightData
INSERT INTO WeightData (WeightType,WeightIncrement)
SELECT DISTINCT OutboardWeightTable,OutboardAmount
FROM ProductionData
WHERE [Datetime] between '180821_150000' and '180822_000000' and (OutboardAmount > 0)
--UPDATE WeightData
--SET Number = (
SELECT COUNT(*)
FROM WeightData a
inner join ProductionData b
ON (a.WeightType = b.OutboardWeightTable) and (a.WeightIncrement = b.OutboardAmount)
WHERE [Datetime] between '180821_150000' and '180822_000000' and (OutboardAmount > 0)
GROUP BY a.WeightType,a.WeightIncrement
--)
I have commented out the lines that are not working, but when I try to run the query I get the following message. I have tried many different ways to do this but I feel this is my closes attempt.
Msg 512, Level 16, State 1, Procedure TestProcedure, Line 24 Subquery
returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
When I run the code after commenting those lines, here is the table and the results screen.
WeightData Table
Results from Query
sql select count
asked Nov 14 '18 at 15:22
MiasmictruthMiasmictruth
32
add a comment |
I am building up a table for statistical analysis of production data. I have extracted all the distinct cases, and constructed a count to find how many times each unique case appears in the table.
My issue comes when trying to UPDATE the table's "Number" column with the results from the COUNT
Here is the code I have so far.
TRUNCATE TABLE WeightData
INSERT INTO WeightData (WeightType,WeightIncrement)
SELECT DISTINCT OutboardWeightTable,OutboardAmount
FROM ProductionData
WHERE [Datetime] between '180821_150000' and '180822_000000' and (OutboardAmount > 0)
--UPDATE WeightData
--SET Number = (
SELECT COUNT(*)
FROM WeightData a
inner join ProductionData b
ON (a.WeightType = b.OutboardWeightTable) and (a.WeightIncrement = b.OutboardAmount)
WHERE [Datetime] between '180821_150000' and '180822_000000' and (OutboardAmount > 0)
GROUP BY a.WeightType,a.WeightIncrement
--)
I have commented out the lines that are not working, but when I try to run the query I get the following message. I have tried many different ways to do this but I feel this is my closes attempt.
Msg 512, Level 16, State 1, Procedure TestProcedure, Line 24 Subquery
returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
When I run the code after commenting those lines, here is the table and the results screen.
WeightData Table
Results from Query
sql select count
asked Nov 14 '18 at 15:22
MiasmictruthMiasmictruth
32
add a comment |
I am building up a table for statistical analysis of production data. I have extracted all the distinct cases, and constructed a count to find how many times each unique case appears in the table.
My issue comes when trying to UPDATE the table's "Number" column with the results from the COUNT
Here is the code I have so far.
TRUNCATE TABLE WeightData
INSERT INTO WeightData (WeightType,WeightIncrement)
SELECT DISTINCT OutboardWeightTable,OutboardAmount
FROM ProductionData
WHERE [Datetime] between '180821_150000' and '180822_000000' and (OutboardAmount > 0)
--UPDATE WeightData
--SET Number = (
SELECT COUNT(*)
FROM WeightData a
inner join ProductionData b
ON (a.WeightType = b.OutboardWeightTable) and (a.WeightIncrement = b.OutboardAmount)
WHERE [Datetime] between '180821_150000' and '180822_000000' and (OutboardAmount > 0)
GROUP BY a.WeightType,a.WeightIncrement
--)
I have commented out the lines that are not working, but when I try to run the query I get the following message. I have tried many different ways to do this but I feel this is my closes attempt.
Msg 512, Level 16, State 1, Procedure TestProcedure, Line 24 Subquery
returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
When I run the code after commenting those lines, here is the table and the results screen.
WeightData Table
Results from Query
sql select count
asked Nov 14 '18 at 15:22
MiasmictruthMiasmictruth
32
I am building up a table for statistical analysis of production data. I have extracted all the distinct cases, and constructed a count to find how many times each unique case appears in the table.
My issue comes when trying to UPDATE the table's "Number" column with the results from the COUNT
Here is the code I have so far.
TRUNCATE TABLE WeightData
INSERT INTO WeightData (WeightType,WeightIncrement)
SELECT DISTINCT OutboardWeightTable,OutboardAmount
FROM ProductionData
WHERE [Datetime] between '180821_150000' and '180822_000000' and (OutboardAmount > 0)
--UPDATE WeightData
--SET Number = (
SELECT COUNT(*)
FROM WeightData a
inner join ProductionData b
ON (a.WeightType = b.OutboardWeightTable) and (a.WeightIncrement = b.OutboardAmount)
WHERE [Datetime] between '180821_150000' and '180822_000000' and (OutboardAmount > 0)
GROUP BY a.WeightType,a.WeightIncrement
--)
I have commented out the lines that are not working, but when I try to run the query I get the following message. I have tried many different ways to do this but I feel this is my closes attempt.
Msg 512, Level 16, State 1, Procedure TestProcedure, Line 24 Subquery
returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
When I run the code after commenting those lines, here is the table and the results screen.
WeightData Table
Results from Query
sql select count
sql select count
asked Nov 14 '18 at 15:22
MiasmictruthMiasmictruth
32
asked Nov 14 '18 at 15:22
MiasmictruthMiasmictruth
32
asked Nov 14 '18 at 15:22
MiasmictruthMiasmictruth
32
asked Nov 14 '18 at 15:22
MiasmictruthMiasmictruth
32
asked Nov 14 '18 at 15:22
MiasmictruthMiasmictruth
32
32
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I think you just want a correlated subquery:
UPDATE WeightData
SET Number = (SELECT COUNT(*)
FROM ProductionData pd
WHERE wd.WeightType = p.OutboardWeightTable and
wd.WeightIncrement = pd.OutboardAmount AND
pd.OutboardAmount > 0 AND
pd.[Datetime] between '180821_150000' and '180822_000000'
)
FROM WeightData wd;
answered Nov 14 '18 at 15:25
Gordon LinoffGordon Linoff
774k35306408
I modified the b and p to pd and it works!! I have spent a shameful amount of time stuck on this thanks a lot!
– Miasmictruth
Nov 14 '18 at 15:38
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%2f53303498%2fin-sql-stuck-trying-to-update-a-table-column-from-the-results-of-a-count%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
I think you just want a correlated subquery:
UPDATE WeightData
SET Number = (SELECT COUNT(*)
FROM ProductionData pd
WHERE wd.WeightType = p.OutboardWeightTable and
wd.WeightIncrement = pd.OutboardAmount AND
pd.OutboardAmount > 0 AND
pd.[Datetime] between '180821_150000' and '180822_000000'
)
FROM WeightData wd;
answered Nov 14 '18 at 15:25
Gordon LinoffGordon Linoff
774k35306408
I modified the b and p to pd and it works!! I have spent a shameful amount of time stuck on this thanks a lot!
– Miasmictruth
Nov 14 '18 at 15:38
add a comment |
I think you just want a correlated subquery:
UPDATE WeightData
SET Number = (SELECT COUNT(*)
FROM ProductionData pd
WHERE wd.WeightType = p.OutboardWeightTable and
wd.WeightIncrement = pd.OutboardAmount AND
pd.OutboardAmount > 0 AND
pd.[Datetime] between '180821_150000' and '180822_000000'
)
FROM WeightData wd;
answered Nov 14 '18 at 15:25
Gordon LinoffGordon Linoff
774k35306408
I modified the b and p to pd and it works!! I have spent a shameful amount of time stuck on this thanks a lot!
– Miasmictruth
Nov 14 '18 at 15:38
add a comment |
I think you just want a correlated subquery:
UPDATE WeightData
SET Number = (SELECT COUNT(*)
FROM ProductionData pd
WHERE wd.WeightType = p.OutboardWeightTable and
wd.WeightIncrement = pd.OutboardAmount AND
pd.OutboardAmount > 0 AND
pd.[Datetime] between '180821_150000' and '180822_000000'
)
FROM WeightData wd;
answered Nov 14 '18 at 15:25
Gordon LinoffGordon Linoff
774k35306408
I think you just want a correlated subquery:
UPDATE WeightData
SET Number = (SELECT COUNT(*)
FROM ProductionData pd
WHERE wd.WeightType = p.OutboardWeightTable and
wd.WeightIncrement = pd.OutboardAmount AND
pd.OutboardAmount > 0 AND
pd.[Datetime] between '180821_150000' and '180822_000000'
)
FROM WeightData wd;
answered Nov 14 '18 at 15:25
Gordon LinoffGordon Linoff
774k35306408
edited Nov 14 '18 at 20:31
answered Nov 14 '18 at 15:25
Gordon LinoffGordon Linoff
774k35306408
answered Nov 14 '18 at 15:25
Gordon LinoffGordon Linoff
774k35306408
answered Nov 14 '18 at 15:25
Gordon LinoffGordon Linoff
774k35306408
774k35306408
I modified the b and p to pd and it works!! I have spent a shameful amount of time stuck on this thanks a lot!
– Miasmictruth
Nov 14 '18 at 15:38
add a comment |
I modified the b and p to pd and it works!! I have spent a shameful amount of time stuck on this thanks a lot!
– Miasmictruth
Nov 14 '18 at 15:38
I modified the b and p to pd and it works!! I have spent a shameful amount of time stuck on this thanks a lot!
– Miasmictruth
Nov 14 '18 at 15:38
I modified the b and p to pd and it works!! I have spent a shameful amount of time stuck on this thanks a lot!
– Miasmictruth
Nov 14 '18 at 15:38
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%2f53303498%2fin-sql-stuck-trying-to-update-a-table-column-from-the-results-of-a-count%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
