Column update from one Table to another based on a ID match

[ahmedmohiduet]

How can I Column update from one Table to another based on a ID match?

I have one equery:
csvq 'update nullbatches join file on nullbatches.Inv_date=file.Inv_date set nullbatches.Btc_no = file.Btc_no where file.P_code!=9 and file.P_code!=42 and nullbatches.P_code!=9 and nullbatches.P_code!=42' ( my csvs: nullbatches.csv, file.csv)

Which says: [L:1 C:20] syntax error: unexpected token "join"

Is it doable?

[ondohotola]

I had a similar issue recently and got around it by something like

UPDATE nullbatches
SET nullbatches.Btc_no = (
   SELECT file.Btc_no 
   FROM file 
   WHERE nullbatches.Inv_date = file.Inv_date
)

[mithrandie]

You can also write the query like this.

UPDATE n
   SET n.Btc_no = f.Btc_no
  FROM nullbatches n
       JOIN file f
         ON f.Inv_date = n.Inv_date
 WHERE NOT f.P_code IN (9, 42)
   AND NOT n.P_code IN (9, 42);

cf. https://mithrandie.github.io/csvq/reference/update-query.html