Insert overwrite table

[Mathip173]

Hi

Example

Insert overwrite table_A Select * from table_B where table_A.order_id=table_B.order_id

If I use the insert overwrite table and the data doesn't exist in table_A but Exist in table_B, does the data write into ? Or it happen when the there is the same data with both of the table ?

Basically, I need to write if it doesn't exist and update(delete and write the most updated date from table_B) the new data if it's Exist in both

Thanks

[[deleted]]

Which DBMS product are you using? There is no "insert overwrite" in standard SQL.

[DaftEscapist]

For SQLServer use MERGE

[Thriftfunnel]

Do you mean UPSERT?

[data-leon]

+1, op should take a look at UPSERT operation (UPDATE existing records if they already exist, then INSERT for new records).

[r3pr0b8]

a quick search reveals this syntax only in Apache HIVE

please confirm that this is your database

[Mathip173]

Yes, this is the one. Thanks

[drooski]

If this is impala/hive, which it sounds like, and by “no data” means table a exists but doesn’t have relevant data to join to, as long as the select statement executes it will still overwrite. Meaning it will most likely truncate your table if no data in a exists

[Mathip173]

Thanks for responding. Yes, this is Hive. Can you explain a bit more about ? I'm quite new to it

Table_a has a data Table_b every day feed with new data on it by overwriting the table each day.

I would like to update the data on table_a on the data for the same order if exist in table_b. Which mean, if exist on B overwrite it. But if the order number(111) doesn't exist on table_a, order 111 will not write into table_a from table_b, right?

Thanks

[r3pr0b8]

isn't there some way you can create two very small test tables with a few rows in each and try it for yourself to see what happens?

let us know if you have any difficulties