SET NAMES gb2312;CREATE TABLE `o` (
`id` int(11) default NULL,
`name` varchar(11) default NULL
) TYPE=MyISAM;insert into `o` values ('11','数据1');
insert into `o` values ('22','数据2');
insert into `o` values ('33','数据3');
insert into `o` values ('44','数据4');
CREATE TABLE `class1` (
`id` int(11) default NULL,
`name` varchar(11) default NULL
) TYPE=MyISAM;insert into `class1` values ('11','数据3');
insert into `class1` values ('22','数据2');
insert into `class1` values ('33','数据4');
insert into `class1` values ('44','数据1');CREATE TABLE `class2` (
`id` int(11) default NULL,
`name` varchar(50) default NULL
) TYPE=MyISAM;insert into `class2` values ('11842542','子数据3');
insert into `class2` values ('22543253','子数据2');
insert into `class2` values ('33245235','子数据4');
insert into `class2` values ('44543254','子数据1');
UPDATE
class2
LEFT JOIN class1 ON class1.id = LEFT(class2.id, 2)
LEFT JOIN o ON class1.name = o.name
SET class2.ID = CONCAT(o.id, RIGHT(class2.id, LENGTH(CAST(class2.id AS CHAR)) - 2)) ;SELECT * FROM class2;DROP TABLE `o`;
DROP TABLE `class1`;
DROP TABLE `class2`;
`id` int(11) default NULL,
`name` varchar(11) default NULL
) TYPE=MyISAM;insert into `o` values ('11','数据1');
insert into `o` values ('22','数据2');
insert into `o` values ('33','数据3');
insert into `o` values ('44','数据4');
CREATE TABLE `class1` (
`id` int(11) default NULL,
`name` varchar(11) default NULL
) TYPE=MyISAM;insert into `class1` values ('11','数据3');
insert into `class1` values ('22','数据2');
insert into `class1` values ('33','数据4');
insert into `class1` values ('44','数据1');CREATE TABLE `class2` (
`id` int(11) default NULL,
`name` varchar(50) default NULL
) TYPE=MyISAM;insert into `class2` values ('11842542','子数据3');
insert into `class2` values ('22543253','子数据2');
insert into `class2` values ('33245235','子数据4');
insert into `class2` values ('44543254','子数据1');
UPDATE
class2
LEFT JOIN class1 ON class1.id = LEFT(class2.id, 2)
LEFT JOIN o ON class1.name = o.name
SET class2.ID = CONCAT(o.id, RIGHT(class2.id, LENGTH(CAST(class2.id AS CHAR)) - 2)) ;SELECT * FROM class2;DROP TABLE `o`;
DROP TABLE `class1`;
DROP TABLE `class2`;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货