mysql update 字段select 内容

最新推荐文章于 2025-03-12 00:39:11 发布

原创最新推荐文章于 2025-03-12 00:39:11 发布 · 590 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一种使用SQL内连接及子查询更新表中特定字段的方法，通过实例展示了如何将复合字段拆分为两个独立的字段，适用于数据库维护和数据结构调整。

UPDATE hm_opportunities
INNER JOIN (
	SELECT
		id,
		SUBSTRING_INDEX(opportunity_city, '-', 1) AS province
	FROM
		hm_opportunities
) b ON hm_opportunities.id = b.id
SET hm_opportunities.province = b.province;
UPDATE hm_opportunities
INNER JOIN (
	SELECT
		id,
		SUBSTRING_INDEX(opportunity_city, '-', -1) AS city
	FROM
		hm_opportunities
) b ON hm_opportunities.id = b.id
SET hm_opportunities.city = b.city;