Sequelize altering a table that counts based on a relationship-CodePudding

So I have the following table:

VoucherID    VoucherBatch
1            v001
2            v001
3            v001
4            v002
5            v002
6            v002
7            v003

And I wanted to add a column to indicate the voucher that voucher's number in that batch, the expected result would be like this

VoucherID    VoucherBatch   VoucherEdition
1            v001           1
2            v001           2
3            v001           3
4            v002           1
5            v002           2
6            v002           3
7            v003           1

Does sequelize support something like this?

CodePudding user response：

First I want to point out that I never used sequelize.js, so I can't answer how to implement such things there. Maybe you know this on your own or someone else can answer this. Anyway, I can show how you can select this "voucherEdition" column and how to create a new column and fill it with the correct value in MySQL. In order to select this value, you can use ROW_NUMBER(), here the query:

SELECT voucherid, voucherbatch,   
ROW_NUMBER() OVER(PARTITION BY voucherbatch) AS voucherEdition 
FROM vouchers;

If you only need this select, you're done. Otherwise you can create the new voucherEdition column:

ALTER TABLE vouchers ADD column voucherEdition INT;

and then fill this new column using the mentioned query, so the update command will look like this:

UPDATE vouchers v JOIN
(SELECT voucherid,  
ROW_NUMBER() OVER(PARTITION BY voucherbatch) AS voucherEdition 
FROM vouchers) x ON v.voucherid = x.voucherid
SET v.voucherEdition = x.voucherEdition;

Here I created an example, so you can see this is working correctly: db<>fiddle