March 13, 2017 at 10:37 am
Both DB's are attached to my SQL Sever 2014 instance.
DB1 = farwest
DB2 = sanroque
I need to update the DB1 table, which holds a column named Price from the DB2 table Price columm. These DB's are the exact same database (layout) (this is from a Point of Sale system)
So I need to take the Price column from farwest and replace the Price column in sanroque. They will be on Inventory1.ItemNum = Inventory2.ItemNum
Any help apprecated.
March 13, 2017 at 10:45 am
Thom A - Monday, March 13, 2017 10:41 AMJust like a normal join and update statement:Update MyTable1
SET MyColumn1 = (SELECT T2.MyColumn2
FROM DB2.dbo.MyTable2 T2
WHERE T2.MyID = MyTable1.MyID);
Update Inventory
SET Inventory.Price = (SELECT Inventory.Price
FROM farwest.dbo.Inventory
WHERE Inventory.ItemNum = Inventory.ItemNum);
Something like this?
Gives me this error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
March 13, 2017 at 10:47 am
chef423 - Monday, March 13, 2017 10:45 AMThom A - Monday, March 13, 2017 10:41 AMJust like a normal join and update statement:Update MyTable1
SET MyColumn1 = (SELECT T2.MyColumn2
FROM DB2.dbo.MyTable2 T2
WHERE T2.MyID = MyTable1.MyID);UPDATE Inventory
FROM farwest.dbo.Inventory.Price
INNER JOIN sanroque.dbo.Inventory.Price
ON farwest.dbo.Inventory.ItemNUm = sanroque.dbo.Inventory.ItemNumSomething like this?
You don't have a SET statement, but yes.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 13, 2017 at 10:51 am
Thom A - Monday, March 13, 2017 10:47 AMchef423 - Monday, March 13, 2017 10:45 AMThom A - Monday, March 13, 2017 10:41 AMJust like a normal join and update statement:Update MyTable1
SET MyColumn1 = (SELECT T2.MyColumn2
FROM DB2.dbo.MyTable2 T2
WHERE T2.MyID = MyTable1.MyID);UPDATE Inventory
FROM farwest.dbo.Inventory.Price
INNER JOIN sanroque.dbo.Inventory.Price
ON farwest.dbo.Inventory.ItemNUm = sanroque.dbo.Inventory.ItemNumSomething like this?
You don't have a SET statement, but yes.
Well, this got me closer:
Update Inventory
SET Price = (SELECT Inventory.Price
FROM farwest.dbo.Inventory T2
WHERE T2.ItemNum = Inventory.ItemNum);
but, getting this error:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Price', table 'sanroque.dbo.Inventory'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
March 13, 2017 at 11:08 am
Anyone?
March 13, 2017 at 11:09 am
chef423 - Monday, March 13, 2017 10:51 AMThom A - Monday, March 13, 2017 10:47 AMchef423 - Monday, March 13, 2017 10:45 AMThom A - Monday, March 13, 2017 10:41 AMJust like a normal join and update statement:Update MyTable1
SET MyColumn1 = (SELECT T2.MyColumn2
FROM DB2.dbo.MyTable2 T2
WHERE T2.MyID = MyTable1.MyID);UPDATE Inventory
FROM farwest.dbo.Inventory.Price
INNER JOIN sanroque.dbo.Inventory.Price
ON farwest.dbo.Inventory.ItemNUm = sanroque.dbo.Inventory.ItemNumSomething like this?
You don't have a SET statement, but yes.
Well, this got me closer:
Update Inventory
SET Price = (SELECT Inventory.Price
FROM farwest.dbo.Inventory T2
WHERE T2.ItemNum = Inventory.ItemNum);but, getting this error:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Price', table 'sanroque.dbo.Inventory'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
can you post the definition of the table 'sanroque.dbo.Inventory'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 13, 2017 at 11:17 am
chef423 - Monday, March 13, 2017 11:08 AMAnyone?
A few minutes would have been nice 😉
The error is telling you that you're trying to INSERT a NULL value into your table, Inventory, most likely because that row isn't in your table sanroque.dbo.Inventory.Price.
I assumed from your statement that every row might have a relevant value in your secondary table, however, that seems to not be true.
You could, therefore, instead do:UPDATE MyTable
SET MyColumn = T2.MyColumn
FROM MyDB2.dbo.MyTable2 T2
JOIN MyTable T1 ON T2.MyID = T1.MyID;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 13, 2017 at 11:55 am
Thom A - Monday, March 13, 2017 11:17 AMchef423 - Monday, March 13, 2017 11:08 AMAnyone?A few minutes would have been nice 😉
The error is telling you that you're trying to INSERT a NULL value into your table, Inventory, most likely because that row isn't in your table sanroque.dbo.Inventory.Price.
I assumed from your statement that every row might have a relevant value in your secondary table, however, that seems to not be true.
You could, therefore, instead do:
UPDATE MyTable
SET MyColumn = T2.MyColumn
FROM MyDB2.dbo.MyTable2 T2
JOIN MyTable T1 ON T2.MyID = T1.MyID;
This seems to have done it:UPDATE sanroque.dbo.Inventory
SET Price = T2.Price
FROM farwest.dbo.Inventory T2
JOIN Inventory T1 ON T2.ItemNum = T1.ItemNum;
Sorry I was impatient! Thanks!
March 13, 2017 at 11:58 am
chef423 - Monday, March 13, 2017 11:55 AMThom A - Monday, March 13, 2017 11:17 AMchef423 - Monday, March 13, 2017 11:08 AMAnyone?A few minutes would have been nice 😉
The error is telling you that you're trying to INSERT a NULL value into your table, Inventory, most likely because that row isn't in your table sanroque.dbo.Inventory.Price.
I assumed from your statement that every row might have a relevant value in your secondary table, however, that seems to not be true.
You could, therefore, instead do:
UPDATE MyTable
SET MyColumn = T2.MyColumn
FROM MyDB2.dbo.MyTable2 T2
JOIN MyTable T1 ON T2.MyID = T1.MyID;This seems to have done it:
UPDATE sanroque.dbo.Inventory
SET Price = T2.Price
FROM farwest.dbo.Inventory T2
JOIN Inventory T1 ON T2.ItemNum = T1.ItemNum;Sorry I was impatient! Thanks!
Actually, that did not work. now all my Price column are '0.00'
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply