prepostsql拆分表中的字段,找出数字,并更新到其他的字段上的sql

x33g5p2x  于2021-11-09 转载在 其他  
字(0.6k)|赞(0)|评价(0)|浏览(131)

现在的需求是将model中的字符拆分,找出数字,并更新到v_cpu的字段上,更新的逻辑是

1、先拆分

SELECT ID, string_to_array( fastone_model, '.' ) AS model FROM reserved_instance

2、再打成行的形式

SELECT ID, UNNEST ( string_to_array( fastone_model, '.' ) ) AS model FROM reserved_instance

3、过滤掉字符的行

SELECT
		ri.model AS bmodel,
		ri.ID AS bid 
	FROM
		( SELECT ID, UNNEST ( string_to_array( fastone_model, '.' ) ) AS model FROM reserved_instance ) ri 
	WHERE
		ri.model ~ '^[^a-z]+$'

4、更新到新的字段

UPDATE reserved_instance 
SET v_cpu = b.bmodel :: INT 
FROM
	(
	SELECT
		ri.model AS bmodel,
		ri.ID AS bid 
	FROM
		( SELECT ID, UNNEST ( string_to_array( fastone_model, '.' ) ) AS model FROM reserved_instance ) ri 
	WHERE
		ri.model ~ '^[^a-z]+$' 
	) b 
WHERE
	b.bid = ID

5、查看是否更新成功

相关文章

微信公众号

最新文章

更多