How to update one table from another

sql

(bruce mcpherson) #1

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 p.id = profession.id;

but that takes me to this error message
https://github.com/cockroachdb/cockroach/issues/7841

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


(Raphael 'kena' Poss) #2
  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 p.id = profession.id

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.

(bruce mcpherson) #3

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