sql - Update Foreign Key based on condition across both tables -

let's have custom fields table, , custom fields can grouped groups. lets have groups table.

let's have convention whereby prefix of name of custom field should groupname_ groupname title column of groups table.

how can update large number of existing custom fields take group id in sql? i've tried this:

update fields, fgroup set group_id = fgroup.id name 'gp1\_%' , substring(name, 0, 4)) = lower(title) , fields.account_id=fgroup.account_id , fields.account_id = 111; 

this fails because update not take multiple tables.

how can list of group names (gp1, gp2, gp3, gp4)? how modify prefix length? (not substring(name, 0, 4), populated third parameter based on length of prefix until first underscore?

your query looks sql-server or mysql syntax. in postgres can update single table in single statement. join in additional tables from clause. details in manual.

for single group (with guesswork each column belongs to)

update fields f set    group = g.id   fgroup g  f.name 'gp1\_%' ,    left(f.name, 4) = lower(g.title) ,    f.account_id = 111 ,    f.account_id = g.account_id; 

for multiple groups , a, quote: populated third parameter based on length of prefix, let's name prefix_len:

update fields f set    group = g.id   fgroup g  left(f.name, prefix_len) = '{gp1,gp2,gp3,gp4}'::text[] ,    left(f.name, prefix_len) = lower(g.title) ,    f.account_id = 111 ,    f.account_id = g.account_id; 

aside: group reserved word, don't use identifier.