Question 162506 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I fill a MySQL table with data from line with rn=2?

I have following table:

    CREATE TABLE `filldubl` (
      `recid` int(11) NOT NULL AUTO_INCREMENT,
      `mykey` varchar(200) DEFAULT NULL,
      `id` varchar(10) DEFAULT NULL,
      `rn` int(11) DEFAULT 0,
      PRIMARY KEY (`recid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    INSERT INTO `filldubl` VALUES ('1', '10.1109/ICLP56858.2022.9942468', '4762', '1');
    INSERT INTO `filldubl` VALUES ('2', null, '5556', '2');
    INSERT INTO `filldubl` VALUES ('3', '10.23919/ECC55457.2022.9838149', '4931', '1');
    INSERT INTO `filldubl` VALUES ('4', null, '9327', '2');
    INSERT INTO `filldubl` VALUES ('5', '223Nakamurar', '12033', '1');
    INSERT INTO `filldubl` VALUES ('6', null, '7720', '2');
    INSERT INTO `filldubl` VALUES ('7', '10.1016/j.cherd.2022.06.019', '4080', '1');
    INSERT INTO `filldubl` VALUES ('8', null, '6168', '2');
    INSERT INTO `filldubl` VALUES ('9', '10.1016/S0959-8049(22)00919-4', '367', '1');
    INSERT INTO `filldubl` VALUES ('10', null, '6775', '2');

Now I would like to fill the Nulls where rn = 2 with the value of mykey of the predecessor row (where rn = 1).

How can this be done?
Thanks a lot.

EDIT: this is a wrong way, I tried update filldubl a,filldubl b set a.mykey=b.mykey where a.recid=b.recid+1 and a.rn=2;

As this changes also the 2.line with the 3. line but I would try to change only (2id+1) -> (2id)

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Whit this update it will works: update filldubl a join filldubl b on (a.recid)=(b.recid-1) and (a.recid/2) <> round(a.recid/2) set a.mykey=b.mykey;


  2. 0

    If you have the rigid pattern described in your question, where the rows to be updated all have even number for recid and rn = 2 then you can be very explicit –

    UPDATE filldubl t1
JOIN filldubl t2 ON t1.recid - 1 = t2.recid AND t2.rn = 1
SET t1.mykey = t2.mykey
WHERE t1.recid % 2 = 0 AND t1.rn = 2 AND t1.mykey IS NULL;

    If you need to be more flexible and update any row where mykey IS NULL with the most recent (based on recid) mykey value, then you could use something like –

    UPDATE filldubl t1
SET mykey = (
    SELECT mykey FROM (
        SELECT mykey
        FROM filldubl
        WHERE recid < t1.recid AND mykey IS NOT NULL
        ORDER BY recid DESC LIMIT 1
    ) t
)
WHERE t1.mykey IS NULL;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search