当前位置: 动力学知识库 > 问答 > 编程问答 >

join - Mysql multi-table UPDATE first record

问题描述:

I have a permission system between two objects (users => firms) with table permissions for linking. Now i need to update firms table with first permission user id. I made this query:

UPDATE parim_firms, parim_permissions

SET parim_firms.firm_user_id = parim_permissions.permission_a_id

WHERE parim_firms.firm_user_id = 0

AND parim_firms.firm_id = parim_permissions.permission_b_id

Now if one firm hash multiple linked users, then will it be updated with the first or last matched user?

My logic says after first update firm_user_id != 0 and that row doesn't get updated anymore.

But im not sure, maybe does it run the query for all joined rows and the last row will stay.

And if it doesn't then how can i modify the query to update with only first matched result?

网友答案:
 UPDATE parim_firms 
 SET parim_firms.firm_user_id = 
  ( 
    select  parim_permissions.permission_a_id from parim_permissions 
    WHERE   parim_firms.firm_id = 0 
    AND parim_firms.firm_id = parim_permissions.permission_b_id 
 )

or

update parim_firms  a
set a.firm_user_id  = b.permission_a_id 
from  parim_permissions  b
WHERE   parim_firms.firm_id = 0 
    AND parim_firms.firm_id = parim_permissions.permission_b_id 
分享给朋友:
您可能感兴趣的文章:
随机阅读: