英文:
How can I fill the table with the data from the line wiht rn=2
问题
以下是翻译好的内容：
现在我有以下表格：
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');
现在，我想要用前一行的mykey值（当rn等于1时）来填充rn等于2的地方的Nulls。
这应该如何完成？
非常感谢。
编辑：这是错误的方法，我尝试了以下SQL语句：
update filldubl a,filldubl b set a.mykey=b.mykey where a.recid=b.recid+1 and a.rn=2;
因为这也会更改第2行与第3行，但我只想更改(2id+1) -> (2id)。
英文:
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)
答案1
得分: 1
以下是您要翻译的内容：
如果您在问题中描述的模式是刚性的，要更新的行都具有recid和rn = 2的偶数值，那么您可以非常明确地执行以下操作：
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;
如果您需要更灵活地更新任何mykey IS NULL的行，并使用最近的（基于recid）mykey值，那么您可以使用类似以下的方法：
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;
英文:
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;
答案2
得分: 0
"With this update, it will work: 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;"
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论