mysql - 在这种情况下如何更新多列

Question

我的数据结构如下图所示

CREATE TABLE IF NOT EXISTS `admin_tax_names` (
  `sl_no` int(11) NOT NULL auto_increment,
  `tax_id` varchar(3) default NULL,
  `tax_name` varchar(50) default NULL,
  `modified_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`sl_no`)
) ;


INSERT INTO `admin_tax_names` (`sl_no`, `tax_id`, `tax_name`, `modified_at`) VALUES
    (1, 'T1', 'cost', '2015-01-22 16:55:19'),
    (2, 'T6', 'Service Tax', '2015-01-22 16:55:19'),
    (3, 'T3', 'VAT', '2015-01-22 16:55:19'),
    (4, 'T4', 'OtherTax2', '2015-01-22 16:55:19'),
    (5, 'T5', 'OtherTax1', '2015-01-22 16:55:20'),
    (6, 'T2', 'Discount', '2015-01-23 19:23:30'),
    (7, 'T7', 'Service Charge', '2015-02-09 20:48:19');

我需要在一条语句中用不同的值更新 T1 、 T2 、 T3 、 T4 、 T5 、 T6 、 T7我试过了

UPDATE admin_tax_names SET T1 = 'cost' , T2 = 'as' , T3 = 'sas' , T4 = 'sas' , T5='sas' , T6 = 'asa' , T7 ='asas';

但是我得到的错误是

“字段列表”中的未知列“T1”

这是我的sqlfiddle

http://sqlfiddle.com/#!9/4c24d

你能告诉我在这种情况下如何更新表吗？？

Answer 1

我想你想要这样的东西:

update admin_tax_names
    set tax_name = (case tax_id
                      when 'T1' then 'cost' 
                      when 'T2' then 'as' 
                      when 'T3' then 'sas'
                      when 'T4' then 'sas'
                      when 'T5' then 'sas'
                      when 'T6' then 'asa'
                      when 'T7' then 'asas'
                  end)
    where tax_id in ('T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'T7');

您似乎混淆了列名和列值之间的区别。