英文:
Query relating to split_part() in PostgreSQL
问题
我有一个包含first_name（名字），last_name（姓氏）和name（姓名）的表格。Name（姓名）包含了完整的姓名，例如"Joe Bloggs"。我想要更新表格中的每一行，将first_name（名字）和last_name（姓氏）从name（姓名）列中提取出来，但是我找不到正确的update和子查询组合来使其工作。
我尝试过使用以下代码：
update users a set a.first_name = subquery.first_name_new
from (select username, split_part(name, ' ',1) as first_name_new from users) as subquery
where a.username = subquery.username
我能理解为什么这不起作用，但是我就是找不到正确的组合。
英文:
I have a table with first_name, last_name and name. Name consists of a full name such as "Joe Bloggs". I want to update every row in the table with first_name and last_name derived from the name column, but I just cant find the right combination of update with subquery to make it work.
I have tried using:
update users a set a.first_name = subquery.first_name_new
from (select username, split_part(name, ' ',1) as first_name_new from users) as subquery
where a.username = subquery.username
I can see why this isn't working, but I just cant find the right combination.
答案1
得分: 1
你可以按照以下方式进行：
更新用户
设置 first_name = split_part(name, ' ', 1),
last_name = split_part(name, ' ', -1)
英文:
You can do it as follows :
update users
set first_name = split_part(name, ' ',1),
last_name = split_part(name, ' ',-1)
答案2
得分: 0
update users
set first_name = split_part(name, ' ', 1),
last_name = split_part(name, ' ', -1)
英文:
update users
set first_name = split_part(name, ' ', 1),
last_name = split_part(name, ' ', -1)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论