Update a column with values of the same column in the same table

In postgres I would like to update as below.

My table product has these columns

uuid

sold

product_id

variant_id

data

I would like to update the uuid of all the sold=true records with the uuid of the records with that product_id and variant_id.

For example

If this is my table
enter image description here

I would like to update the records with product_id=203 and variant_id = 1 to have the same uuid. And the records with product_id = 3242 and variant_id=3 to have the same uuid and so on.

How should the update query be like?

How should the update query be like? even if either of product_id or variant_id is NULL??

Postgresql version 10.3

edited Jan 2 at 10:37

asked Dec 31 '18 at 8:57

Surya

356213

Say that the first row with product_id = 3243 instead had NULL as product_id, what should happen when updating? Since variant_id is the same for product_id NULL, 3242 and 6630 how would you know which rows to update?

– Joakim Danielson
Jan 2 at 12:00

What is the primary key of the table?

– Joakim Danielson
Jan 2 at 12:06

Can only rows with sold = 'f' have null values?

– Joakim Danielson
Jan 2 at 12:15

add a comment |

In postgres I would like to update as below.

My table product has these columns

uuid

sold

product_id

variant_id

data

I would like to update the uuid of all the sold=true records with the uuid of the records with that product_id and variant_id.

For example

If this is my table
enter image description here

I would like to update the records with product_id=203 and variant_id = 1 to have the same uuid. And the records with product_id = 3242 and variant_id=3 to have the same uuid and so on.

How should the update query be like?

How should the update query be like? even if either of product_id or variant_id is NULL??

Postgresql version 10.3

edited Jan 2 at 10:37

asked Dec 31 '18 at 8:57

Surya

356213

Say that the first row with product_id = 3243 instead had NULL as product_id, what should happen when updating? Since variant_id is the same for product_id NULL, 3242 and 6630 how would you know which rows to update?

– Joakim Danielson
Jan 2 at 12:00

What is the primary key of the table?

– Joakim Danielson
Jan 2 at 12:06

Can only rows with sold = 'f' have null values?

– Joakim Danielson
Jan 2 at 12:15

add a comment |

In postgres I would like to update as below.

My table product has these columns

uuid

sold

product_id

variant_id

data

I would like to update the uuid of all the sold=true records with the uuid of the records with that product_id and variant_id.

For example

If this is my table
enter image description here

I would like to update the records with product_id=203 and variant_id = 1 to have the same uuid. And the records with product_id = 3242 and variant_id=3 to have the same uuid and so on.

How should the update query be like?

How should the update query be like? even if either of product_id or variant_id is NULL??

Postgresql version 10.3

edited Jan 2 at 10:37

asked Dec 31 '18 at 8:57

Surya

356213

In postgres I would like to update as below.

My table product has these columns

uuid

sold

product_id

variant_id

data

I would like to update the uuid of all the sold=true records with the uuid of the records with that product_id and variant_id.

For example

If this is my table
enter image description here

I would like to update the records with product_id=203 and variant_id = 1 to have the same uuid. And the records with product_id = 3242 and variant_id=3 to have the same uuid and so on.

How should the update query be like?

How should the update query be like? even if either of product_id or variant_id is NULL??

Postgresql version 10.3

sql postgresql postgresql-10

edited Jan 2 at 10:37

asked Dec 31 '18 at 8:57

Surya

356213

edited Jan 2 at 10:37

asked Dec 31 '18 at 8:57

Surya

356213

edited Jan 2 at 10:37

asked Dec 31 '18 at 8:57

Surya

356213

asked Dec 31 '18 at 8:57

Surya

356213

asked Dec 31 '18 at 8:57

Surya

356213

Say that the first row with product_id = 3243 instead had NULL as product_id, what should happen when updating? Since variant_id is the same for product_id NULL, 3242 and 6630 how would you know which rows to update?

– Joakim Danielson
Jan 2 at 12:00

What is the primary key of the table?

– Joakim Danielson
Jan 2 at 12:06

Can only rows with sold = 'f' have null values?

– Joakim Danielson
Jan 2 at 12:15

add a comment |

Say that the first row with product_id = 3243 instead had NULL as product_id, what should happen when updating? Since variant_id is the same for product_id NULL, 3242 and 6630 how would you know which rows to update?

– Joakim Danielson
Jan 2 at 12:00

What is the primary key of the table?

– Joakim Danielson
Jan 2 at 12:06

Can only rows with sold = 'f' have null values?

– Joakim Danielson
Jan 2 at 12:15

Say that the first row with product_id = 3243 instead had NULL as product_id, what should happen when updating? Since variant_id is the same for product_id NULL, 3242 and 6630 how would you know which rows to update?

– Joakim Danielson
Jan 2 at 12:00

What is the primary key of the table?

– Joakim Danielson
Jan 2 at 12:06

Can only rows with sold = 'f' have null values?

– Joakim Danielson
Jan 2 at 12:15

add a comment |

2 Answers
2

active

oldest

votes

Use UPDATE with a join to the same table. This query updates the rows where sold = 't', if I have misunderstood that you need to switch 't' and 'f' in the last two lines

UPDATE product AS p 

SET uuid = p2.uuid

FROM product p2

WHERE p2.product_id = p.product_id

  AND (p2.variant_id = p.variant_id  OR p2.variant_id IS NULL OR p.variant_id IS NULL)

  AND p.sold = 't'

  AND p2.sold = 'f'

db-fiddle

edited Jan 2 at 12:07

answered Dec 31 '18 at 9:07

Joakim Danielson

10.2k3725

This doesn't work if either the product_id is NULL or variant_id is NULL.

– Surya
Jan 2 at 10:25

@Surya What does it mean when product_id is NULL, could those rows really be updated?

– Joakim Danielson
Jan 2 at 11:55

@Surya I have updated my answer to allow for variant_id to be null but not for product_id to be null because I don't understand how that is possible to perform a correct update then. Could you show some sample data with null values?

– Joakim Danielson
Jan 2 at 12:08

@Surya in your question you say even if either of product_id or variant_id is NULL isn't this correct?

– forpas
Jan 2 at 13:21

add a comment |

I assume there is no case for product_id and variant_id to be both NULL:

update product p1 set uuid = p2.uuid from product p2

where p1.sold = 't' and p2.sold = 'f'

  and (

    (p2.product_id = p1.product_id and p2.variant_id = p1.variant_id)

    or

    (p2.product_id IS NULL and p2.variant_id = p1.variant_id)

    or

    (p2.product_id = p1.product_id and p2.variant_id IS NULL)

  )

answered Jan 2 at 10:54

forpas

17.8k3728

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%2f53985456%2fupdate-a-column-with-values-of-the-same-column-in-the-same-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

Use UPDATE with a join to the same table. This query updates the rows where sold = 't', if I have misunderstood that you need to switch 't' and 'f' in the last two lines

UPDATE product AS p 

SET uuid = p2.uuid

FROM product p2

WHERE p2.product_id = p.product_id

  AND (p2.variant_id = p.variant_id  OR p2.variant_id IS NULL OR p.variant_id IS NULL)

  AND p.sold = 't'

  AND p2.sold = 'f'

db-fiddle

edited Jan 2 at 12:07

answered Dec 31 '18 at 9:07

Joakim Danielson

10.2k3725

This doesn't work if either the product_id is NULL or variant_id is NULL.

– Surya
Jan 2 at 10:25

@Surya What does it mean when product_id is NULL, could those rows really be updated?

– Joakim Danielson
Jan 2 at 11:55

@Surya I have updated my answer to allow for variant_id to be null but not for product_id to be null because I don't understand how that is possible to perform a correct update then. Could you show some sample data with null values?

– Joakim Danielson
Jan 2 at 12:08

@Surya in your question you say even if either of product_id or variant_id is NULL isn't this correct?

– forpas
Jan 2 at 13:21

add a comment |

Use UPDATE with a join to the same table. This query updates the rows where sold = 't', if I have misunderstood that you need to switch 't' and 'f' in the last two lines

UPDATE product AS p 

SET uuid = p2.uuid

FROM product p2

WHERE p2.product_id = p.product_id

  AND (p2.variant_id = p.variant_id  OR p2.variant_id IS NULL OR p.variant_id IS NULL)

  AND p.sold = 't'

  AND p2.sold = 'f'

db-fiddle

edited Jan 2 at 12:07

answered Dec 31 '18 at 9:07

Joakim Danielson

10.2k3725

This doesn't work if either the product_id is NULL or variant_id is NULL.

– Surya
Jan 2 at 10:25

@Surya What does it mean when product_id is NULL, could those rows really be updated?

– Joakim Danielson
Jan 2 at 11:55

@Surya I have updated my answer to allow for variant_id to be null but not for product_id to be null because I don't understand how that is possible to perform a correct update then. Could you show some sample data with null values?

– Joakim Danielson
Jan 2 at 12:08

@Surya in your question you say even if either of product_id or variant_id is NULL isn't this correct?

– forpas
Jan 2 at 13:21

add a comment |

Use UPDATE with a join to the same table. This query updates the rows where sold = 't', if I have misunderstood that you need to switch 't' and 'f' in the last two lines

UPDATE product AS p 

SET uuid = p2.uuid

FROM product p2

WHERE p2.product_id = p.product_id

  AND (p2.variant_id = p.variant_id  OR p2.variant_id IS NULL OR p.variant_id IS NULL)

  AND p.sold = 't'

  AND p2.sold = 'f'

db-fiddle

edited Jan 2 at 12:07

answered Dec 31 '18 at 9:07

Joakim Danielson

10.2k3725

Use UPDATE with a join to the same table. This query updates the rows where sold = 't', if I have misunderstood that you need to switch 't' and 'f' in the last two lines

UPDATE product AS p 

SET uuid = p2.uuid

FROM product p2

WHERE p2.product_id = p.product_id

  AND (p2.variant_id = p.variant_id  OR p2.variant_id IS NULL OR p.variant_id IS NULL)

  AND p.sold = 't'

  AND p2.sold = 'f'

db-fiddle

edited Jan 2 at 12:07

answered Dec 31 '18 at 9:07

Joakim Danielson

10.2k3725

edited Jan 2 at 12:07

answered Dec 31 '18 at 9:07

Joakim Danielson

10.2k3725

answered Dec 31 '18 at 9:07

Joakim Danielson

10.2k3725

answered Dec 31 '18 at 9:07

Joakim Danielson

10.2k3725

This doesn't work if either the product_id is NULL or variant_id is NULL.

– Surya
Jan 2 at 10:25

@Surya What does it mean when product_id is NULL, could those rows really be updated?

– Joakim Danielson
Jan 2 at 11:55

@Surya I have updated my answer to allow for variant_id to be null but not for product_id to be null because I don't understand how that is possible to perform a correct update then. Could you show some sample data with null values?

– Joakim Danielson
Jan 2 at 12:08

@Surya in your question you say even if either of product_id or variant_id is NULL isn't this correct?

– forpas
Jan 2 at 13:21

add a comment |

This doesn't work if either the product_id is NULL or variant_id is NULL.

– Surya
Jan 2 at 10:25

@Surya What does it mean when product_id is NULL, could those rows really be updated?

– Joakim Danielson
Jan 2 at 11:55

@Surya I have updated my answer to allow for variant_id to be null but not for product_id to be null because I don't understand how that is possible to perform a correct update then. Could you show some sample data with null values?

– Joakim Danielson
Jan 2 at 12:08

@Surya in your question you say even if either of product_id or variant_id is NULL isn't this correct?

– forpas
Jan 2 at 13:21

This doesn't work if either the product_id is NULL or variant_id is NULL.

– Surya
Jan 2 at 10:25

@Surya What does it mean when product_id is NULL, could those rows really be updated?

– Joakim Danielson
Jan 2 at 11:55

@Surya I have updated my answer to allow for variant_id to be null but not for product_id to be null because I don't understand how that is possible to perform a correct update then. Could you show some sample data with null values?

– Joakim Danielson
Jan 2 at 12:08

@Surya in your question you say even if either of product_id or variant_id is NULL isn't this correct?

– forpas
Jan 2 at 13:21

add a comment |

I assume there is no case for product_id and variant_id to be both NULL:

update product p1 set uuid = p2.uuid from product p2

where p1.sold = 't' and p2.sold = 'f'

  and (

    (p2.product_id = p1.product_id and p2.variant_id = p1.variant_id)

    or

    (p2.product_id IS NULL and p2.variant_id = p1.variant_id)

    or

    (p2.product_id = p1.product_id and p2.variant_id IS NULL)

  )

answered Jan 2 at 10:54

forpas

17.8k3728

add a comment |

I assume there is no case for product_id and variant_id to be both NULL:

update product p1 set uuid = p2.uuid from product p2

where p1.sold = 't' and p2.sold = 'f'

  and (

    (p2.product_id = p1.product_id and p2.variant_id = p1.variant_id)

    or

    (p2.product_id IS NULL and p2.variant_id = p1.variant_id)

    or

    (p2.product_id = p1.product_id and p2.variant_id IS NULL)

  )

answered Jan 2 at 10:54

forpas

17.8k3728

add a comment |

I assume there is no case for product_id and variant_id to be both NULL:

update product p1 set uuid = p2.uuid from product p2

where p1.sold = 't' and p2.sold = 'f'

  and (

    (p2.product_id = p1.product_id and p2.variant_id = p1.variant_id)

    or

    (p2.product_id IS NULL and p2.variant_id = p1.variant_id)

    or

    (p2.product_id = p1.product_id and p2.variant_id IS NULL)

  )

answered Jan 2 at 10:54

forpas

17.8k3728

I assume there is no case for product_id and variant_id to be both NULL:

update product p1 set uuid = p2.uuid from product p2

where p1.sold = 't' and p2.sold = 'f'

  and (

    (p2.product_id = p1.product_id and p2.variant_id = p1.variant_id)

    or

    (p2.product_id IS NULL and p2.variant_id = p1.variant_id)

    or

    (p2.product_id = p1.product_id and p2.variant_id IS NULL)

  )

answered Jan 2 at 10:54

forpas

17.8k3728

answered Jan 2 at 10:54

forpas

17.8k3728

answered Jan 2 at 10:54

forpas

17.8k3728

answered Jan 2 at 10:54

forpas

17.8k3728

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