5

I have two tables, one with data about counties and another with data about states. Different states can sometimes have counties with the same exact name, so I am trying to populate a unique_name column in my counties table that is the concatenation of a county name and the abbreviation of the state where that county is located (e.g.: Honolulu County, HI).

I have come up with the following query:

ALTER TABLE counties
    UPDATE unique_name =
    (
        SELECT concat(counties.name, ', ', states.name_abbr)
        FROM counties
        INNER JOIN states
        ON counties.statefp = states.statefp
    ) WHERE unique_name = ''

However, I keep getting the following error:

DB::Exception: Unknown identifier: states.statefp, context: required_names: 'states.statefp' source_tables: table_aliases: private_aliases: column_aliases: public_columns: masked_columns: array_join_columns: source_columns: .

The inner query is working perfectly fine on its own, but I don't why this error is coming up when I try to do the update. Any ideas?

MarcioPorto
  • 555
  • 7
  • 22

1 Answers1

10

ClickHouse does not support dependent joins for ALTER TABLE UPDATE. Fortunately, there is a workaround. You have to create a special Join engine table for the update. Something like this:

CREATE TABLE states_join as states Engine = Join(ANY, LEFT, statefp);

INSERT INTO states_join SELECT * from states;

ALTER TABLE counties 
UPDATE unique_name = concat(name, joinGet('states_join', 'name_abbr', statefp))
WHERE unique_name = '';

DROP TABLE states_join;

Note, it only works in 19.x versions.

Alexander Zaitsev
  • 301
  • 1
  • 2
  • The concat part works as expected (tested by `select concat(name, joinGet('states_join', 'name_abbr', statefp)) from counties`). However, the update statement does not actually update any rows in the `unique_name` column. – MarcioPorto Apr 22 '19 at 15:07
  • Could you check your system.mutations table? Any error here? – Alexander Zaitsev Apr 23 '19 at 07:36
  • @MarcioPorto maybe you're trying to update the column which is a part of ORDER BY key? – filimonov Jun 05 '19 at 15:38