postgresql - 在 postgresql 9.3 中更新多列的问题

Question

我想根据电话表更新配置文件表中的三列。它不起作用并收到错误消息:

ERROR:  syntax error at or near "home_phone"
LINE 2:                       set home_phone = (select number from p..

电话表示例:

_id phone.type  phone.number
51b20fac6ddbfdb704000034    Office  403-244-1895
57b6204d7065a6a5550fe30b    Mobile  123-4567
57b6204d7065a6a5550fe30b    Home    765-4321
57e99f88b948da6c3be04366    Mobile  250-851-1041
51b20fac6ddbfdb704000007    Home    555-555-5555

脚本:

update profiles f set office_phone = (select number from phone_tmp p where p._id = f._userid and type ='Office'),
                      set home_phone = (select number from phone_tmp p where p._id = f._userid and type ='home'),
                      set mobile =(select number from phone_tmp p where p._id = f._userid and type ='mobile') ;    

phone 表中_id 的值与profiles 表中的_userid 相同。我感谢任何帮助。谢谢。

Answer 1

如错误所述 - 查询语法错误。试试这个:

update profiles f set office_phone = (select number from phone_tmp p where p._id = f._userid and type ='Office'),
                   home_phone = (select number from phone_tmp p where p._id = f._userid and type ='home'),
                   mobile =(select number from phone_tmp p where p._id = f._userid and type ='mobile'); 

您应该只使用一个 SET 关键字并用逗号分隔特定的赋值。