mysql - 从另一个表错误列的mysql更新不能为空

Question

我有这些表结构：

point
===============================================
column  | type | comment
===============================================
id      | int  | primary key (auto increment)
type    | enum | 'paradise', 'culinary'
post_id | int  | foreign key to culinary.id
user_id | int  |
===============================================

culinary
===============================================
column  | type         | comment
===============================================
id      | int          | primary key (auto inc)
title   | varchar(255) | 
user_id | int          |
===============================================

我想point.user_id使用来自的值更新字段culinary.user_id。但我收到一条错误消息，说column 'user_id' cannot be null即使我选择每个表时，也没有包含 null user_id 的记录...

这是我尝试过的：

-- first attempt gets error message > 1048 - Column 'user_id' cannot be null
update `point`
set `user_id` = (
  select `culinary`.`user_id`
  from `culinary`
  where `culinary`.`id` = `point`.`post_id`
  and `point`.`type` = 'culinary'
)

-- second attempt but still the same error message
update `point`
set `user_id` = (
  select `culinary`.`user_id`
  from `culinary`
  where `culinary`.`id` = `point`.`post_id`
)
where `type` = 'culinary'

标签： mysqljoincrud

Answer 1

使用连接：

update `point` p
inner join `culinary` c on c.`id` = p.`post_id`
set p.`user_id` = c.`user_id`
where p.`type` = 'culinary'