0

I have table in which i want to update records based on it's 'wishlist_id' value. Sample format of table records. This is sample records of table before update

Expected table record after update.

Expected output

So can you help to update this record using mysql query?

Improve this question
2
  • Does id can be used to identify the records where wishlist value must be populated? does all records with name != 'wishlist' have wishlist IS NULL?
    – Akina
    Commented Oct 16, 2019 at 13:02
  • Without any way to link the separate entities to their variables there is no way to update this after the data has been entered. Your options are to add the wishlist ID at the time you do the initial INSERT or restructure your tables so that they work in a normalised way and keep the relevant data in separate tables linked by the wishlist_id
    – KevH
    Commented Oct 16, 2019 at 13:03

1 Answer 1

Reset to default
0

Assuming that all records with wishlist IS NULL must be updated with the value taken from the closest previous (while sorting by id ASC) record with non-NULL wishlist value:

UPDATE test
SET wishlist = (@tmp := COALESCE(wishlist, @tmp))
WHERE (@tmp := -1)
ORDER BY id ASC

fiddle

Improve this answer
1
  • .thanks , this query is working, Thanks again!
    – 5a01d01P
    Commented Oct 17, 2019 at 5:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.