Update column with the sum of another one

I used PgAdmin4 to develop a PostGIS database.
I tried to update a column (called "surface_net") in a table called "parcelles" with the sum of another column (called "surface_cultures") in another table called "zones_cultures". The table "zones_cultures" has a foreign key with "parcelles" ("zones_cultures.id_parcelles" = "parcelles.id_egrid").

To sum up, the column "surface_net" is the sum of "zones_cultures" group by the "id_egrid".

So I did this SQL query to update the column "surface_net":

UPDATE public.parcelles

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        group by parcelles.id_egrid);

But it doesn't work. Here is the result:

ERROR:  more than one row returned by a subquery used as an expression

SQL state: 21000

Could someone help me with my SQL query to be able to update the column "surface_net"?
Thanks in advance

edited Nov 20 '18 at 13:35

a_horse_with_no_name

295k46451545

asked Nov 20 '18 at 13:31

Sophie Compagnon

1

Skip the GROUP BY

– Joakim Danielson
Nov 20 '18 at 13:35

Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example

– Juan Carlos Oropeza
Nov 20 '18 at 13:38

add a comment |

To sum up, the column "surface_net" is the sum of "zones_cultures" group by the "id_egrid".

So I did this SQL query to update the column "surface_net":

UPDATE public.parcelles

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        group by parcelles.id_egrid);

But it doesn't work. Here is the result:

ERROR:  more than one row returned by a subquery used as an expression

SQL state: 21000

Could someone help me with my SQL query to be able to update the column "surface_net"?
Thanks in advance

edited Nov 20 '18 at 13:35

a_horse_with_no_name

295k46451545

asked Nov 20 '18 at 13:31

Sophie Compagnon

1

Skip the GROUP BY

– Joakim Danielson
Nov 20 '18 at 13:35

Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example

– Juan Carlos Oropeza
Nov 20 '18 at 13:38

add a comment |

To sum up, the column "surface_net" is the sum of "zones_cultures" group by the "id_egrid".

So I did this SQL query to update the column "surface_net":

UPDATE public.parcelles

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        group by parcelles.id_egrid);

But it doesn't work. Here is the result:

ERROR:  more than one row returned by a subquery used as an expression

SQL state: 21000

Could someone help me with my SQL query to be able to update the column "surface_net"?
Thanks in advance

edited Nov 20 '18 at 13:35

a_horse_with_no_name

295k46451545

asked Nov 20 '18 at 13:31

Sophie Compagnon

To sum up, the column "surface_net" is the sum of "zones_cultures" group by the "id_egrid".

So I did this SQL query to update the column "surface_net":

UPDATE public.parcelles

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        group by parcelles.id_egrid);

But it doesn't work. Here is the result:

ERROR:  more than one row returned by a subquery used as an expression

SQL state: 21000

Could someone help me with my SQL query to be able to update the column "surface_net"?
Thanks in advance

sql postgresql sql-update qsqlquery postgresql-11

edited Nov 20 '18 at 13:35

a_horse_with_no_name

295k46451545

asked Nov 20 '18 at 13:31

Sophie Compagnon

edited Nov 20 '18 at 13:35

a_horse_with_no_name

295k46451545

asked Nov 20 '18 at 13:31

Sophie Compagnon

edited Nov 20 '18 at 13:35

a_horse_with_no_name

295k46451545

edited Nov 20 '18 at 13:35

a_horse_with_no_name

295k46451545

edited Nov 20 '18 at 13:35

a_horse_with_no_name

295k46451545

asked Nov 20 '18 at 13:31

Sophie Compagnon

asked Nov 20 '18 at 13:31

Sophie Compagnon

asked Nov 20 '18 at 13:31

Sophie Compagnon

1

Skip the GROUP BY

– Joakim Danielson
Nov 20 '18 at 13:35

Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example

– Juan Carlos Oropeza
Nov 20 '18 at 13:38

add a comment |

1

Skip the GROUP BY

– Joakim Danielson
Nov 20 '18 at 13:35

Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example

– Juan Carlos Oropeza
Nov 20 '18 at 13:38

Skip the GROUP BY

– Joakim Danielson
Nov 20 '18 at 13:35

Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example

– Juan Carlos Oropeza
Nov 20 '18 at 13:38

add a comment |

3 Answers
3

active

oldest

votes

You need a WHERE clause in the subquery that makes it return 1 relevant result for 1 row of the table you're updating. I believe this should work:

UPDATE public.parcelles p

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

        group by parcelles.id_egrid);

answered Nov 20 '18 at 13:38

wvdz

12.6k23062

You dont need GROUP BY if already filter by egrid

– Juan Carlos Oropeza
Nov 20 '18 at 13:41

Thanks a lot it worked!

– Sophie Compagnon
Nov 21 '18 at 9:23

I'm glad it did. Don't forget to upvote and accept if this worked for you.

– wvdz
Nov 21 '18 at 11:46

Just a last question: do you know how I could create an "auto update" with this code? I want that every time I add new values in the "zones_cultures", the "parcelles" table is update automatically with the update you gave me.

– Sophie Compagnon
Nov 21 '18 at 16:11

@SophieCompagnon In general, you can do this in two ways: have parcelles be a view instead of a table, or by using a trigger. Using triggers should usually be avoided because they are a maintenance nightmare. So the best way to handle this is to create a view. If you need more help it would probably be best to create a new question for this.

– wvdz
Nov 24 '18 at 0:11

add a comment |

when you applied group by it returned multiple values as a result it thrown error, just remove group by it will work

UPDATE public.parcelles

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        );

Or if you want to update per id then use below no need group by

UPDATE public.parcelles p

    SET p.surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

       );

answered Nov 20 '18 at 13:38

Zaynul Abadin Tuhin

12.2k2931

add a comment |

You can try below

UPDATE public.parcelles p

    SET surface_net=sum(zones_cultures.surface_cultures)

 inner join zones_cultures where p.id_egrid = zones_cultures.id_parcelles

answered Nov 20 '18 at 13:39

fa06

12.2k2917

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53394141%2fupdate-column-with-the-sum-of-another-one%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

You need a WHERE clause in the subquery that makes it return 1 relevant result for 1 row of the table you're updating. I believe this should work:

UPDATE public.parcelles p

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

        group by parcelles.id_egrid);

answered Nov 20 '18 at 13:38

wvdz

12.6k23062

You dont need GROUP BY if already filter by egrid

– Juan Carlos Oropeza
Nov 20 '18 at 13:41

Thanks a lot it worked!

– Sophie Compagnon
Nov 21 '18 at 9:23

I'm glad it did. Don't forget to upvote and accept if this worked for you.

– wvdz
Nov 21 '18 at 11:46

Just a last question: do you know how I could create an "auto update" with this code? I want that every time I add new values in the "zones_cultures", the "parcelles" table is update automatically with the update you gave me.

– Sophie Compagnon
Nov 21 '18 at 16:11

@SophieCompagnon In general, you can do this in two ways: have parcelles be a view instead of a table, or by using a trigger. Using triggers should usually be avoided because they are a maintenance nightmare. So the best way to handle this is to create a view. If you need more help it would probably be best to create a new question for this.

– wvdz
Nov 24 '18 at 0:11

add a comment |

You need a WHERE clause in the subquery that makes it return 1 relevant result for 1 row of the table you're updating. I believe this should work:

UPDATE public.parcelles p

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

        group by parcelles.id_egrid);

answered Nov 20 '18 at 13:38

wvdz

12.6k23062

You dont need GROUP BY if already filter by egrid

– Juan Carlos Oropeza
Nov 20 '18 at 13:41

Thanks a lot it worked!

– Sophie Compagnon
Nov 21 '18 at 9:23

I'm glad it did. Don't forget to upvote and accept if this worked for you.

– wvdz
Nov 21 '18 at 11:46

Just a last question: do you know how I could create an "auto update" with this code? I want that every time I add new values in the "zones_cultures", the "parcelles" table is update automatically with the update you gave me.

– Sophie Compagnon
Nov 21 '18 at 16:11

@SophieCompagnon In general, you can do this in two ways: have parcelles be a view instead of a table, or by using a trigger. Using triggers should usually be avoided because they are a maintenance nightmare. So the best way to handle this is to create a view. If you need more help it would probably be best to create a new question for this.

– wvdz
Nov 24 '18 at 0:11

add a comment |

You need a WHERE clause in the subquery that makes it return 1 relevant result for 1 row of the table you're updating. I believe this should work:

UPDATE public.parcelles p

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

        group by parcelles.id_egrid);

answered Nov 20 '18 at 13:38

wvdz

12.6k23062

You need a WHERE clause in the subquery that makes it return 1 relevant result for 1 row of the table you're updating. I believe this should work:

UPDATE public.parcelles p

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

        group by parcelles.id_egrid);

answered Nov 20 '18 at 13:38

wvdz

12.6k23062

answered Nov 20 '18 at 13:38

wvdz

12.6k23062

answered Nov 20 '18 at 13:38

wvdz

12.6k23062

answered Nov 20 '18 at 13:38

wvdz

12.6k23062

You dont need GROUP BY if already filter by egrid

– Juan Carlos Oropeza
Nov 20 '18 at 13:41

Thanks a lot it worked!

– Sophie Compagnon
Nov 21 '18 at 9:23

I'm glad it did. Don't forget to upvote and accept if this worked for you.

– wvdz
Nov 21 '18 at 11:46

Just a last question: do you know how I could create an "auto update" with this code? I want that every time I add new values in the "zones_cultures", the "parcelles" table is update automatically with the update you gave me.

– Sophie Compagnon
Nov 21 '18 at 16:11

@SophieCompagnon In general, you can do this in two ways: have parcelles be a view instead of a table, or by using a trigger. Using triggers should usually be avoided because they are a maintenance nightmare. So the best way to handle this is to create a view. If you need more help it would probably be best to create a new question for this.

– wvdz
Nov 24 '18 at 0:11

add a comment |

You dont need GROUP BY if already filter by egrid

– Juan Carlos Oropeza
Nov 20 '18 at 13:41

Thanks a lot it worked!

– Sophie Compagnon
Nov 21 '18 at 9:23

I'm glad it did. Don't forget to upvote and accept if this worked for you.

– wvdz
Nov 21 '18 at 11:46

Just a last question: do you know how I could create an "auto update" with this code? I want that every time I add new values in the "zones_cultures", the "parcelles" table is update automatically with the update you gave me.

– Sophie Compagnon
Nov 21 '18 at 16:11

@SophieCompagnon In general, you can do this in two ways: have parcelles be a view instead of a table, or by using a trigger. Using triggers should usually be avoided because they are a maintenance nightmare. So the best way to handle this is to create a view. If you need more help it would probably be best to create a new question for this.

– wvdz
Nov 24 '18 at 0:11

You dont need GROUP BY if already filter by egrid

– Juan Carlos Oropeza
Nov 20 '18 at 13:41

Thanks a lot it worked!

– Sophie Compagnon
Nov 21 '18 at 9:23

I'm glad it did. Don't forget to upvote and accept if this worked for you.

– wvdz
Nov 21 '18 at 11:46

Just a last question: do you know how I could create an "auto update" with this code? I want that every time I add new values in the "zones_cultures", the "parcelles" table is update automatically with the update you gave me.

– Sophie Compagnon
Nov 21 '18 at 16:11

@SophieCompagnon In general, you can do this in two ways: have parcelles be a view instead of a table, or by using a trigger. Using triggers should usually be avoided because they are a maintenance nightmare. So the best way to handle this is to create a view. If you need more help it would probably be best to create a new question for this.

– wvdz
Nov 24 '18 at 0:11

add a comment |

when you applied group by it returned multiple values as a result it thrown error, just remove group by it will work

UPDATE public.parcelles

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        );

Or if you want to update per id then use below no need group by

UPDATE public.parcelles p

    SET p.surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

       );

answered Nov 20 '18 at 13:38

Zaynul Abadin Tuhin

12.2k2931

add a comment |

when you applied group by it returned multiple values as a result it thrown error, just remove group by it will work

UPDATE public.parcelles

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        );

Or if you want to update per id then use below no need group by

UPDATE public.parcelles p

    SET p.surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

       );

answered Nov 20 '18 at 13:38

Zaynul Abadin Tuhin

12.2k2931

add a comment |

when you applied group by it returned multiple values as a result it thrown error, just remove group by it will work

UPDATE public.parcelles

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        );

Or if you want to update per id then use below no need group by

UPDATE public.parcelles p

    SET p.surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

       );

answered Nov 20 '18 at 13:38

Zaynul Abadin Tuhin

12.2k2931

when you applied group by it returned multiple values as a result it thrown error, just remove group by it will work

UPDATE public.parcelles

    SET surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        );

Or if you want to update per id then use below no need group by

UPDATE public.parcelles p

    SET p.surface_net=

    (SELECT sum(zones_cultures.surface_cultures)

        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles

        where parcelles.id_egrid = p.id_egrid

       );

answered Nov 20 '18 at 13:38

Zaynul Abadin Tuhin

12.2k2931

answered Nov 20 '18 at 13:38

Zaynul Abadin Tuhin

12.2k2931

answered Nov 20 '18 at 13:38

Zaynul Abadin Tuhin

12.2k2931

answered Nov 20 '18 at 13:38

Zaynul Abadin Tuhin

12.2k2931

add a comment |

You can try below

UPDATE public.parcelles p

    SET surface_net=sum(zones_cultures.surface_cultures)

 inner join zones_cultures where p.id_egrid = zones_cultures.id_parcelles

answered Nov 20 '18 at 13:39

fa06

12.2k2917

add a comment |

You can try below

UPDATE public.parcelles p

    SET surface_net=sum(zones_cultures.surface_cultures)

 inner join zones_cultures where p.id_egrid = zones_cultures.id_parcelles

answered Nov 20 '18 at 13:39

fa06

12.2k2917

add a comment |

You can try below

UPDATE public.parcelles p

    SET surface_net=sum(zones_cultures.surface_cultures)

 inner join zones_cultures where p.id_egrid = zones_cultures.id_parcelles

answered Nov 20 '18 at 13:39

fa06

12.2k2917

You can try below

UPDATE public.parcelles p

    SET surface_net=sum(zones_cultures.surface_cultures)

 inner join zones_cultures where p.id_egrid = zones_cultures.id_parcelles

answered Nov 20 '18 at 13:39

fa06

12.2k2917

answered Nov 20 '18 at 13:39

fa06

12.2k2917

answered Nov 20 '18 at 13:39

fa06

12.2k2917

answered Nov 20 '18 at 13:39

fa06

12.2k2917

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu