I need to update my new database with data from 1 column in my old database. Basically based on matching ItemID's I need to set the Description column in my new DB with the values in the old DB. I can see what needs to be updated when I do a join but I am not sure how to handle this update properly.
Here is a SQL query for inserting data from one database table into another database table. INSERT INTO SQL statement is used to insert data from one database table to another. The INSERT INTO can be used to transfer data from one database to other database or between two tables in the same database.
UPDATE operations with subqueries that reference the same table object are supported only if all of the following conditions are true: The subquery either returns a single row, or else has no correlated column references. The subquery is in the UPDATE statement WHERE clause, using Condition with Subquery syntax.
The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.
BEGIN TRANSACTION
UPDATE t1
SET Description = t2.Description
FROM db1.dbo.foo t1
JOIN db2.dbo.foo t2
ON t1.ItemID = t2.ItemID
SELECT * FROM db1.dbo.foo
--prevents changes from being committed
ROLLBACK
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With