mysql 将值添加到SQL中现有表中新添加的列

uemypmqf 于 2022-12-17 发布在 Mysql

关注(0)|答案(3)|浏览(127)

INSERT INTO Employees (empId,name,salary,Email_id,DOB)

VALUES
('1','stephen','30000','stephen@gmail.com','1999-02-16'),
('2','andew','40000','andew@gmail.com','1995-12-09'),
('3','dev','16000','dev@gmail.com','1992-08-16'),
('4','jackson','25000','jack@gmail.com','1992-08-16'),
('5','shayam','33000','shaym@gmail.com','1992-08-16'),
('6','abc','31000','abc@gmail.com','1992-08-16'),
('7','xyz','32500','xyz@gmail.com','1992-08-16'),
('8','San','22000','san123@gmail.com','1992-08-16'),
('9','Sonu','29000','hkyadav@gmail.com','1992-08-16');

现在，我在现有的表中添加了一列。

ALTER TABLE EMPLOYEES
        ADD age varchar(10);
    
    
    INSERT INTO EMPLOYEES (age)
    VALUES
    (
    ('21'),
    ('22'),
    ('32'),
    ('35'),
    ('45'),
    ('39'),
    ('28'),
    ('26'),
    ('36')
    );

收到错误代码：1136.列计数与第1行的值计数不匹配

mysql

来源：https://stackoverflow.com/questions/68053323/adding-values-to-newly-added-column-in-exsting-table-in-sql

3条答案

按热度按时间

mzsu5hc01#

当insert语句中的列数小于查询中的值数时，将引发上述异常。请检查列并尝试首次手动插入值，然后运行insert脚本。

赞(0）回复(0）举报 2022-12-17

eqfvzcg82#

正确的语法为

INSERT INTO EMPLOYEES (age)
VALUES
(21),
(22),
(32),
(35),
(45),
(39),
(28),
(26),
(36)
;

这将添加除age列之外的所有列都为NULL的新行，或者如果某些列被声明为NOT NULL且没有DEFAULT值，则会失败。如果目标不是添加新行而是更新现有行，则需要UPDATE语句

update EMPLOYEES e,
   (values   
    row (1, 21),
    row (2, 22),
    row (3, 32),
    row (4, 35),
    row (5, 45),
    row (6, 39),
    row (7, 28),
    row (8, 26),
    row (9, 36) 
   ) t(empId, age)
set e.age = t.age
where e.empId = t.empId;

赞(0）回复(0）举报 2022-12-17

szqfcxe23#

update EMPLOYEES e,
   (values   
    row (1, 21),
    row (2, 22),
    row (3, 32),
    row (4, 35),
    row (5, 45),
    row (6, 39),
    row (7, 28),
    row (8, 26),
    row (9, 36) 
   ) t(empId, age)
set e.age = t.age
where e.empId = t.empId;

赞(0）回复(0）举报 2022-12-17

我来回答

mysql 将值添加到SQL中现有表中新添加的列

3条答案

相关问题

热门标签

最新问答