How to update from another table on a condition

Hi,

I’d like to update a column of table a from table b on the condition that a.id = b.id.

In mysql, I could use

UPDATE table_a
INNER JOIN table_b
SET ...

but this seems not workable in CockroachDB.

Anyone knows how to get this done?

Thanks in advance.

Hi @swdaily,

Welcome to Cockroach Forum!

In CockroachDB, the syntax to do this is similar to postgres

UPDATE table_a AS a 
SET price=b.price_per_item
FROM table_b AS b
WHERE a.item_id = b.item_id;

For example:

> create table table_a (item_id int, price int);
> create table table_b (item_id int, price_per_item int);
> insert into table_a (item_id, price) values (1,10);
> insert into table_a (item_id, price) values (2,20);
> insert into table_b (item_id, price_per_item) values (1,11);
> insert into table_b (item_id, price_per_item) values (2,22);
> select * from table_a;
  item_id | price
+---------+-------+
        1 |    10
        2 |    20

> select * from table_b;
  item_id | price_per_item
+---------+----------------+
        1 |             11
        2 |             22

> UPDATE table_a AS a SET price=b.price_per_item FROM table_b AS b WHERE a.item_id = b.item_id;

> select * from table_a;
  item_id | price
+---------+-------+
        1 |    11
        2 |    22

Please let me know if this is what you were looking for.

Regards,
Florence
Technical Support Engineer

Hi @florence-crl,

Thanks for your example! It looks what I’m trying to do.

But I’d like to use subquery in FROM.

For example:

UPDATE table_a
SET counts=b.counts
FROM (
  select id, counts
  FROM table_b
  order by date desc limit 1
) as b
WHERE id = b.id;

Error message is : ERROR: pq: unimplemented at or near "where"

Not sure if it’s due to db version difference.

Thanks!

Hi @swdaily,

I do not know what version you are using, but with version 19.2.3 I was able to do the following:

create table table_a (id int, counts int);
create table table_b (id int, counts int, "date" date);
insert into table_a (id, counts) values (1,10);
insert into table_a (id, counts) values (2,20);
insert into table_b (id, counts, "date") values (1,11, '2020-02-10');
insert into table_b (id, counts, "date") values (1,1, '2020-02-09');
insert into table_b (id, counts, "date") values (2,22, '2020-02-08');

Then I ran your update query and got a different error message:

UPDATE table_a
SET counts=b.counts
FROM (
  select id, counts
  FROM table_b
  order by date desc limit 1
) as b
WHERE id = b.id;
pq: column reference "id" is ambiguous (candidates: table_a.id, b.id)

I modified your query and it ran successfully:

UPDATE table_a as a
SET counts=b.counts
FROM (
  select id, counts
  FROM table_b
  order by date desc limit 1
) as b
WHERE a.id = b.id;

So try running the modified query with the qualified table_a as a and a.id.

Please let me know if it runs for you.