March 11, 2010 at 1:01 pm
You will quickly see I am not a developer, but somehow I have been sucked into this. Here is what I am trying to do:
I have a table in Oracle 8.1.7 that I need to update. The info that I need to update with is in a SQL Server 2005 table. I have the Oracle server set up as a linked server.
I need to update a column named LST_BUY_COST in the Oracle table from a column called PS_COST in the SQL table. The tables are linked by a column named ITM_CD.
ITM_CD is a unique identifier in both tables.
I also need to update a column named LST_BUY_CST_CHNG_DT to today’s date for each record that gets updated.
This is the latest iteration of SQL that I have tried:
Update OracleServer..OracleSchema.GM_ITM
SET LST_BUY_CST = (SELECT a.PS_COST
FROM SQLServer.SQLDatabase.dbo.GRS_COST_COMPARE a
INNER JOIN OracleServer..OracleSchema.GM_ITM b ON b.ITM_CD=a.ITM_CD),
LST_BUY_CST_CHNG_DT=GETDATE()
WHERE ITM_CD='042470'
AND
EXISTS
(SELECT a.PS_COST
FROM SQLServer.SQLDatabase.dbo.GRS_COST_COMPARE a
INNER JOIN OracleServer..OracleSchema.GM_ITM b ON b.ITM_CD=a.ITM_CD)
The qualifier for ITM_CD=’042470” is so I can check to see if the one gets updated.
This is the error being thrown:
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.
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
March 11, 2010 at 2:14 pm
Update OracleServer..OracleSchema.GM_ITM
SET LST_BUY_CST = (SELECT a.PS_COST
FROM SQLServer.SQLDatabase.dbo.GRS_COST_COMPARE a
INNER JOIN OracleServer..OracleSchema.GM_ITM b ON b.ITM_CD=a.ITM_CD),
LST_BUY_CST_CHNG_DT=GETDATE()
WHERE ITM_CD='042470'
AND
EXISTS
(SELECT a.PS_COST
FROM SQLServer.SQLDatabase.dbo.GRS_COST_COMPARE a
INNER JOIN OracleServer..OracleSchema.GM_ITM b ON b.ITM_CD=a.ITM_CD)
The portion I put in bold in the quote above is your issue. As the error message states, it's returning more than 1 value.
In simpler terms, you trying to set A = (c,d,e)
Check out your join conditions a bit more and make certain that query only ever returns 1 value, otherwise you will receive the error you are receiving.
-Luke.
edited to cut down the size of the quote for readability
March 12, 2010 at 1:11 pm
Thanks, Luke.
Sometimes, I know what the message says, but I can't quite get my head around it.
I finally did it in two part (told you I am new at this).
First bit of code updated all of the costs and the second bit reset the dates.
UPDATE OracleServer..OracleSchema.GM_ITM
SET LST_BUY_CST = a.PS_COST
FROM SQLServer.SQLDatabase.dbo.GRS_COST_COMPARE a
INNER JOIN OracleServer..OracleSchema.GM_ITM b ON b.ITM_CD=a.ITM_CD
UPDATE OracleServer..OracleSchema.GM_ITM
SET LST_BUY_CST_CHNG_DT=GETDATE()
WHERE ITM_CD IN (SELECT ITM_CD FROM SQLServer.SQLDatabase.dbo.GRS_COST_COMPARE)
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
March 12, 2010 at 2:26 pm
So I may not be understanding your data all that well, some sample data and the relevant DDL of the tables would make this more helpful, check the first link in my signature for more information, but why not just do it like this...
UPDATE OracleServer..OracleSchema.GM_ITM
SET LST_BUY_CST = a.PS_COST,
LST_BUY_CST_CHNG_DT=GETDATE()
FROM SQLServer.SQLDatabase.dbo.GRS_COST_COMPARE a
INNER JOIN OracleServer..OracleSchema.GM_ITM b
ON b.ITM_CD=a.ITM_CD
Seems to me this might be what you are looking for based on your last 2 update statements. Additionally it does some extra things for you. 1) if something happens between statement 1 and 2 in your query (power outtage, error pick your 'something') your data will be inconsistent. 2) this shoudl perform a bit better than all of the nested subqueries, but on that point your mileage may vary based on your data, tables, indexes etc...
-Luke.
March 13, 2010 at 5:55 am
Thanks again Luke. I will give this a try Monday and see if it works.
I will let you know.
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
March 15, 2010 at 8:45 am
Luke,
I tested the amended code and it works great. Thanks a bunch.
This was my first try at updating a linked Oracle server based on a join with a SQL 2005 table.
I think I was trying to make it way more difficult than it actually was
Thanks again.
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
March 15, 2010 at 8:49 am
I'm thinking so as well. Granted, Oracle has it's own funny ways of doing things, but at the end of the day it's just a database. Once the security is set correctly for the most part updating a table via a linked server is just like updating any other table.
Glad you got it sorted.
-Luke.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply