UPDATE OPENQUERY multiple table selection possible ?
-
Hi, Have no problem running the openquery code below.
Declare @query varchar(8000)
Declare @pID varchar(20)
Declare @pLINE varchar(5)
Declare @oDESIREDRCVDATE varchar(10)
Declare @pSTATUSX varchar(1)
Declare @pSTATUSC varchar(1)
Set @pID='NYDELLTEST'
Set @pLINE='1'
Set @oDESIREDRCVDATE='2006-03-25 00:00:00.000'
Set @pSTATUSX='X'
Set @pSTATUSC='C'
set @query = 'UPDATE OPENQUERY(VMFG, ''SELECT DESIRED_RECV_DATE from PURC_ORDER_LINE WHERE PURC_ORDER_ID=''''' + @pID + ''''' AND LINE_NO=''''' + @pLINE + ''''''')'
set @query = @query + 'SET DESIRED_RECV_DATE = '''+@oDESIREDRCVDATE + ''''
exec(@query)
If however you need to include a second table for the update selection criteria (join on a field) what is the syntax or is this not possible ?
Thnaks, Steve.
-
I don't know your answer, but I experienced being in your shoes once before. I ended up speaking with the company which made the ODBC driver. In that case (it was connecting to a MySql database) I was told that that feature wasn't supported.
Good luck,
Paul
- Paul
http://paulpaivasql.blogspot.com/
-
The "command" is sent directly to the ODBC source without parsing from sql server.
-
I would use a join probably.
... from PURC_ORDER_LINE POL Inner Join OtherTable OT ON OT.SomeField = POL.SomeField.
Then use the values from OT to populate the POL update.
-
Do you have the correct syntax ? Have not been able to get this to run without error.
-
Since you didn't post the syntax you used, how would we know!
Check out the join syntax with BOL. Typically, a simple join syntax is [Inner],[Outer Left],[Outer Right] Join ON FirstTable.fieldname = SecondTable.fieldname
ps. I added the alias' which would then need to be added to the rest of the statement or SQL will return Ambigious statements.
-
If you have not done an openquery update with multiple tables then you do not know what were talking about. This has nothing to do with a standard join, or update.
-
What ODBC driver is it? Oracle, Mysql ,...
-
Yes, the syntax of the join inside the query string you execute via OpenSQL must be for the target DBMS - which I guess is not SQL Server... Some DBMSs are crippled and do not support some really obvious query syntax (eg MySQL - last time I worked with it anyway... Don't flame me! :blush
Is this second table in your local MSSQL server or on the target DBMS?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply