How to update one table from another

I’m trying to update one table from another, and I just can’t get the cockroachdb flavoured syntax correct despite having used many combinations. I apologize in advance for the basic nature of this question, but I’m stumped.
In Postgres, I’d do something like this.
update p set “itemOrder” = profession.“itemOrder” from profession where =;

but that takes me to this error message

Obviously there’s a simple solution to this problem but it eludes me. Appreciate any advice.

  1. assuming the primary key on p is some column x:
upsert into p(x, "itemOrder")
   select p.x, profession."itemOrder"
      from p, profession where =

note that this assumes that there is exactly 1 match between p and profession. I think this is the same assumption as update ... from.

  1. meanwhile, note that CockroachDB is not exactly happy with transactions that update a lot of rows. If there are more than, say, 1000-10000 rows to update, you would need to send multiple statements and add WHERE clauses in each of them to limit the number of rows to update.
1 Like

Thank you. It works. I would never have figured that out!

1 Like