CREATE TABLE `products_sell_order_sub` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `i_main_id` bigint(20) unsigned NOT NULL DEFAULT '0', `i_cltid` int(11) NOT NULL DEFAULT '0', `c_pd_code` char(30) NOT NULL DEFAULT '-', `i_size_01` int(5) NOT NULL DEFAULT '0', `i_size_02` int(5) NOT NULL DEFAULT '0', `i_size_03` int(5) NOT NULL DEFAULT '0', `i_size_04` int(5) NOT NULL DEFAULT '0', `i_size_05` int(5) NOT NULL DEFAULT '0', `i_size_06` int(5) NOT NULL DEFAULT '0', `i_size_07` int(5) NOT NULL DEFAULT '0', `i_size_08` int(5) NOT NULL DEFAULT '0', `i_size_09` int(5) NOT NULL DEFAULT '0', `i_size_10` int(5) NOT NULL DEFAULT '0', `i_size_11` int(5) NOT NULL DEFAULT '0', `i_size_12` int(5) NOT NULL DEFAULT '0', `i_size_13` int(5) NOT NULL DEFAULT '0', `i_size_14` int(5) NOT NULL DEFAULT '0', `i_size_15` int(5) NOT NULL DEFAULT '0', `i_size_16` int(5) NOT NULL DEFAULT '0', `i_size_17` int(5) NOT NULL DEFAULT '0', `i_size_18` int(5) NOT NULL DEFAULT '0', `i_pairs` int(11) NOT NULL DEFAULT '0', `i_total` int(10) NOT NULL DEFAULT '0', `i_price` decimal(8,2) NOT NULL DEFAULT '0.00', `d_chk_date` date NOT NULL DEFAULT '2000-01-01', `d_sent_date` date NOT NULL DEFAULT '2000-01-01', #交货期 `c_extcode` char(25) NOT NULL DEFAULT '-',#客户货号 `c_logo` char(30) NOT NULL DEFAULT '-',#品牌 `c_to_address` char(10) NOT NULL DEFAULT '-', `c_info` char(200) NOT NULL DEFAULT '-', `i_kind` int(10) NOT NULL DEFAULT '0', #回单、计划、首次订单 `i_order_kind` int(3) unsigned NOT NULL default '0',#订单类别(内单、外单) 另表注明单据运算方法 `i_order_dept` int(3) unsigned NOT NULL default '0',#下单部门 `time` TIMESTAMP(14),#更新时间标记 `status` tinyint(3) unsigned NOT NULL default '0',#状态 PRIMARY KEY (`id`), KEY `idx_products_sell_order_sub_on_i_cltid` (`i_cltid`), KEY `idx_products_sell_order_sub_on_i_main_id` (`i_order_dept`,`i_main_id`), KEY `idx_products_sell_order_sub_on_c_pd_code` (`c_pd_code`), KEY `idx_products_sell_order_on_d_chk_date` (`d_chk_date`), KEY `idx_products_sell_order_on_d_sent_date` (`d_sent_date`), KEY `idx_products_sell_order_sub_on_i_order_kind` (`i_order_kind`), KEY `idx_products_sell_order_sub_on_i_order_dept` (`i_order_dept`), KEY `idx_products_sell_order_sub_on_time` (`time`), KEY `idx_products_sell_order_sub_on_status` (`status`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 这是我的表结构,我想让符合i_main_id相同的那些数据里面的i_size_01~~~i_size_18进行修改。
执行前先备份连接数据库就免了分两步走好了1,SELECT distinct(i_main_id) FROM products_sell_order_sub WHERE 1//找到了所有不同的i_main_id2、循环结果,对每一个i_main_id设定相应的值,UPDATE products_sell_order_sub SET i_size_01='', i_size_02='',………………i_size_18='' WHERE i_main_id=''
循环
拼sql
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`i_main_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`i_cltid` int(11) NOT NULL DEFAULT '0',
`c_pd_code` char(30) NOT NULL DEFAULT '-',
`i_size_01` int(5) NOT NULL DEFAULT '0',
`i_size_02` int(5) NOT NULL DEFAULT '0',
`i_size_03` int(5) NOT NULL DEFAULT '0',
`i_size_04` int(5) NOT NULL DEFAULT '0',
`i_size_05` int(5) NOT NULL DEFAULT '0',
`i_size_06` int(5) NOT NULL DEFAULT '0',
`i_size_07` int(5) NOT NULL DEFAULT '0',
`i_size_08` int(5) NOT NULL DEFAULT '0',
`i_size_09` int(5) NOT NULL DEFAULT '0',
`i_size_10` int(5) NOT NULL DEFAULT '0',
`i_size_11` int(5) NOT NULL DEFAULT '0',
`i_size_12` int(5) NOT NULL DEFAULT '0',
`i_size_13` int(5) NOT NULL DEFAULT '0',
`i_size_14` int(5) NOT NULL DEFAULT '0',
`i_size_15` int(5) NOT NULL DEFAULT '0',
`i_size_16` int(5) NOT NULL DEFAULT '0',
`i_size_17` int(5) NOT NULL DEFAULT '0',
`i_size_18` int(5) NOT NULL DEFAULT '0',
`i_pairs` int(11) NOT NULL DEFAULT '0',
`i_total` int(10) NOT NULL DEFAULT '0',
`i_price` decimal(8,2) NOT NULL DEFAULT '0.00',
`d_chk_date` date NOT NULL DEFAULT '2000-01-01',
`d_sent_date` date NOT NULL DEFAULT '2000-01-01', #交货期
`c_extcode` char(25) NOT NULL DEFAULT '-',#客户货号
`c_logo` char(30) NOT NULL DEFAULT '-',#品牌
`c_to_address` char(10) NOT NULL DEFAULT '-',
`c_info` char(200) NOT NULL DEFAULT '-',
`i_kind` int(10) NOT NULL DEFAULT '0', #回单、计划、首次订单
`i_order_kind` int(3) unsigned NOT NULL default '0',#订单类别(内单、外单) 另表注明单据运算方法
`i_order_dept` int(3) unsigned NOT NULL default '0',#下单部门
`time` TIMESTAMP(14),#更新时间标记
`status` tinyint(3) unsigned NOT NULL default '0',#状态
PRIMARY KEY (`id`),
KEY `idx_products_sell_order_sub_on_i_cltid` (`i_cltid`),
KEY `idx_products_sell_order_sub_on_i_main_id` (`i_order_dept`,`i_main_id`),
KEY `idx_products_sell_order_sub_on_c_pd_code` (`c_pd_code`),
KEY `idx_products_sell_order_on_d_chk_date` (`d_chk_date`),
KEY `idx_products_sell_order_on_d_sent_date` (`d_sent_date`),
KEY `idx_products_sell_order_sub_on_i_order_kind` (`i_order_kind`),
KEY `idx_products_sell_order_sub_on_i_order_dept` (`i_order_dept`),
KEY `idx_products_sell_order_sub_on_time` (`time`),
KEY `idx_products_sell_order_sub_on_status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
这是我的表结构,我想让符合i_main_id相同的那些数据里面的i_size_01~~~i_size_18进行修改。