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 8 (of 8 total)

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