Update values in DataFrame with values from another DataFrame

[AleksandraFrolo]

Hi, I have to data sources: "admin table" which contains active and inactive employees and I have "hr table" which contains only active employees.

I need to update admin table with data from hr table:

You can see that I need to update employee with number 153, so manager should be update from David to Laura.

I use method update(), but I get error 

How can I solve the problem?

Thanks

[Kaniz]

Hi @AleksandraFrolo , To update your "admin" table with data from the "hr" table, you can use the MERGE INTO statement provided by Databricks Delta. This operation is known as an upsert (update or insert). Here's an example of how you can do it:

sql
MERGE INTO admin_table USING hr_table ON admin_table.employee_number = hr_table.employee_number
WHEN MATCHED THEN 
    UPDATE SET admin_table.manager = hr_table.manager
WHEN NOT MATCHED THEN 
    INSERT *;

This SQL statement will do the following:
- It will match rows from "admin_table" and "hr_table" based on the employee number.
- If a match is found, it will update the manager in the "admin_table" with the manager from the "hr_table".
- If no match is found, it will insert the row from "hr_table" into "admin_table".Please replace admin_table, hr_table, employee_number, and manager with your actual table names and column names.

Sources:
- [Upsert to a table](https://docs.databricks.com/delta/tutorial.html)
- [Modify all unmatched rows using merge](https://docs.databricks.com/delta/merge.html)