Solved

Compare and update data between two databases

Posted on 2016-10-04
6
76 Views
Last Modified: 2016-10-04
Hi,

I was helped by one of the members with the query below, but a closer look at the query doesn't works. Any assistance greatly appreciated. Thanks!

Here is the query:

USE Inventory
GO
UPDATE Items
SET [Available Inventory] = (SELECT [Available Inventory]
        FROM Items_Update.. Updates U
        WHERE U.[Item Number] = Items.[Item Number])


Example per screenshot:
- The query compare the 'Item Number' between the Inventory and Items_Update databases.
- If the Item Number matches, then update the "Available Inventory" number, which is 22 to the "Available Inventory" field in the Inventory database.
0
Comment
Question by:Member_2_7967487
  • 3
  • 3
6 Comments
 

Author Comment

by:Member_2_7967487
ID: 41829185
Example1.jpg
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41829191
Pls try this..

USE Inventory
GO

UPDATE a
SET a.[Available Inventory] = U.[Available Inventory]
FROM Items a
INNER JOIN Items_Update.. Updates U
ON U.[Item Number] = a.[Item Number] 

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41829210
Have you tried the above approach?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Member_2_7967487
ID: 41829214
I have an error.



errorA.jpg
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41829216
can you provide me the schema for both the tables ?
0
 

Author Comment

by:Member_2_7967487
ID: 41829220
I updated the line below and it works!  Thank you very much, Pawan!!
[ Available Inventory ]

SET a.[Available Inventory] = U.[ Available Inventory ]
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question