Merging tables on specific fields in PrestoDB

Question

I want to combine two different tables.

table1

name	id	type	date
name1	1	a	08-17
name2	2	b	08-17

table2

name	id	date	attribute_1
name1	1	08-18	attr_id_1
name2	2	08-18	attr_id_2

The two tables have in common "name" and "id" fields (used for mapping the records) and the field "date" is the partition key for both.

I need to carry out two operations:

add a column in table1
insert the records with "attribute_1" in table2

Expected Output:

name	id	type	date	attribute_1
name1	1	a	08-17
name2	2	b	08-17
name1	1	a	08-18	attr_id_1
name2	2	b	08-18	attr_id_2

The inserted records should be in the new partition (new "date"). The "type" column in output table is duplicated from table1(actually there are other fields like "type" in table1 but for simplicity I just list one of them), it's like updating the rows in table1 with the "attribute_1" field, then insert the updated rows back into table1.

I've looked at the INSERT INTO command in the official documentation but it seems that there is no such operation that does it all, what should I do?

Thanks in advance!

How do you generate the "*type*" field for table2 values in your output? What's the rationale behind those values? — lemon, Sep 21 '22 at 18:07
Actually it's like duplicate the record in table1, and append the "attribute_1" in table2 — dhlbryan, Sep 21 '22 at 18:27

score 1 · Answer 1 · answered Sep 21 '22 at 18:38

You can split the problem into two queries, namely:

an ALTER TABLE statement, to change the schema of table1
an INSERT statement, to add rows from table2 to table1

The select statement inside the insert will require a join operation to generate "table1.type" values on table2. The match is carried out on the fields "name" and "id".

ALTER TABLE table1 ADD COLUMN IF NOT EXISTS attribute_1 <same type of table2.attribute1>;

INSERT INTO table1 
SELECT t2.name, 
       t2.id,
       t1.type, 
       t2.date,
       t2.attribute_1
FROM       table2 t2
INNER JOIN table1 t1
        ON t2.name = t1.name AND t2.id = t1.id

Merging tables on specific fields in PrestoDB

1 Answers1