UPDATE OPENQUERY multiple table selection possible ?

Question

Post reply

UPDATE OPENQUERY multiple table selection possible ?

saustin

Right there with Babe

Points: 765

March 12, 2006 at 10:17 am

#365558

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Paul Paiva SSCrazy Points: 2237 More actions · Answer 1

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/

Jo Pattyn SSC-Dedicated Points: 32341 More actions · Answer 2

The "command" is sent directly to the ODBC source without parsing from sql server.

Steve Fine Ten Centuries Points: 1164 More actions · Answer 3

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.

saustin Right there with Babe Points: 765 More actions · Answer 4

Do you have the correct syntax ? Have not been able to get this to run without error.

Steve Fine Ten Centuries Points: 1164 More actions · Answer 5

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.

saustin Right there with Babe Points: 765 More actions · Answer 6

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.

Jo Pattyn SSC-Dedicated Points: 32341 More actions · Answer 7

Jo Pattyn

SSC-Dedicated

Points: 32341

March 14, 2006 at 9:54 am

#626590

What ODBC driver is it? Oracle, Mysql ,...

Ian Yates SSCoach Points: 19738 More actions · Answer 8

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?