SQL UPDATE报错#1241：操作数应包含1列，求语句修正方案

Hey there, let's sort out that error you're running into! The root issue here is in your subquery for the M_ID assignment: you're selecting two columns (M.M_ID, M.LABEL) but MySQL only expects a single column to assign to the M_ID field (since it's a single-value column in the utilisateurs table).

修正后的SQL语句 #

UPDATE utilisateurs 
SET NAME = 't', 
    SURNAME = 't', 
    LOGIN = 't', 
    PASSWORD = 't', 
    TYPE = 'Formateur', 
    M_ID = (SELECT M.M_ID FROM matieres AS M WHERE M.LABEL = 'Culture_General') 
WHERE U_ID = 3;

额外注意事项 #

• 要确保matieres表中LABEL = 'Culture_General'的记录只有一条，如果存在多条匹配的记录，子查询会返回多行，又会触发Subquery returns more than 1 row错误。这种情况下，你可以用LIMIT 1来限制结果，或者调整WHERE条件确保唯一匹配：

UPDATE utilisateurs 
SET NAME = 't', 
    SURNAME = 't', 
    LOGIN = 't', 
    PASSWORD = 't', 
    TYPE = 'Formateur', 
    M_ID = (SELECT M.M_ID FROM matieres AS M WHERE M.LABEL = 'Culture_General' LIMIT 1) 
WHERE U_ID = 3;

内容的提问来源于stack exchange，提问作者DevJ