是否可能基于MySQL的某一列上的选择进行插入? [英] is insert based on select on one of the column in MySQL possible?

问题描述

下面是基于对MySQL可能的其中一列进行选择的插入

Is following insert based on select on one of the column possible in MySQL

INSERT INTO student_fees(id, name, fees) 
VALUES(1, SELECT name from students where student_id = 1, 200$) 

如果是，那么示例确实有用.

If yes then and example will really help.

-谢谢

推荐答案

尝试INSERT...SELECT语句

INSERT INTO student_fees(id, name, fees) 
SELECT ... -- put here the SELECT STATEMENT with condition

如果列ID为auto incremented，则不必指定1，否则会导致错误.

if your column ID is auto incremented, you don't have to specify the 1 or else it will cause you an error.

INSERT INTO student_fees(name, fees) 
SELECT `name`, '200$' 
FROM students         -- this will select all students on the table
                      -- and add $200 on thier fees.

另一点是，如果您只想从student的表中插入一列，则需要指定条件，这样就不会出现约束错误(假设您的列ID是主键)

Another point is, if you only want to insert one column from the student's table, you need yo specify the condition, so you will not get constraint error (assuming your column ID is the Primary Key)

INSERT INTO student_fees(name, fees) 
SELECT `name`, '200$' 
FROM   students
WHERE  columnName = 'blahBlah'

更新1

看到您的评论，您有此查询

Seeing your comment, you have this query

INSERT INTO coupon_allotment (page_id, offer_id, coupon_code, user_id) 
SELECT page_id, 4, 'ABC'        -- number of columns mismatch, right?
FROM pages_discounts_association 
WHERE discount_id = 4

您需要删除上方的user_id列，或者您需要添加 select语句中的ID以便匹配列数.

you need to remove the user_id column above OR you need to add an ID in your select statement in order to match the number of columns.

这篇关于是否可能基于MySQL的某一列上的选择进行插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！