0

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Pramodi Samaratunga
  • 515
  • 4
  • 13
  • 2
    check this question https://stackoverflow.com/questions/7606175/how-to-compare-data-between-two-table-in-different-databases-using-sql-server-20 – J M Dec 10 '20 at 10:20
  • Please tag your question with the database you are running: oracle, sqlserver, postgresql...? – GMB Dec 10 '20 at 10:23
  • 1
    Does this answer your question? [How to compare data between two table in different databases using Sql Server 2008?](https://stackoverflow.com/questions/7606175/how-to-compare-data-between-two-table-in-different-databases-using-sql-server-20) – trincot Dec 10 '20 at 10:29

1 Answers1

0

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
)
GMB
  • 216,147
  • 25
  • 84
  • 135