Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

Compare and update data between two databases

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
Member_2_7967487
Asked:
Member_2_7967487
  • 3
  • 3
1 Solution
 
Member_2_7967487Author Commented:
Example1.jpg
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Have you tried the above approach?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Member_2_7967487Author Commented:
I have an error.



errorA.jpg
0
 
Pawan KumarDatabase ExpertCommented:
can you provide me the schema for both the tables ?
0
 
Member_2_7967487Author Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now