Search code examples
sqlsql-serverdatabasesql-updatesubquery

Compare Two Tables In two Databases with the same Table Structure

I have two databases Database_1 and Database_2. Both of these two databases have the table asset_table with the same columns and same table structure. But there is some extra data in Database_2 asset_table than in Database_1 asset_table.

I need to update some columns in extra data in "Database_2" as '0'.

Here is my update query.

Update Database_2.q2m1.asset_table
set min_qty = 0, max_qty = 0, unit_cost = '0.00'

I need a query to check what are the extra data are included in Database_2 and not in Database_1.

The primary key for both tables is asset_is. Then update the data.

Could anyone help me with a script for this?

Solution

  • I think you want not exists:

    update a2
set min_qty = 0, max_qty = 0, unit_cost = 0 
from database2.asset_table a2
where not exists (
    select 1 from database1.asset_table a1 where a1.asset_id = a2.asset_id
)