Openquery update using a join.

• Scott Rouse

  SSC Veteran

  Points: 239

  More actions

  July 24, 2006 at 5:25 pm

  #173638

  I need to update a Sybase ASE table from a MS Sql server (2000 now, 2005 soon).

  I can get inserts to work, but how do I update ASE from MS?

  The only examples I can find are trivial. Can I update with a join?

  In a nutshell..

  Both databases have an emp table with columns emp_id{pkey}, emp_name, emp_status

  ASE linked server is ASEls, ASE server is ASE, db is myasedb

  MS server is MS, db is mymsdb

  insert openquery(ASEls,'select emp_id, emp_name, emp_status' from myasedb..emp where 1=0')

  select emp_id, emp_name, emp_status from mymsdb

  [note: there is more to the query that inserts only new rows, but it would just get in the way here]

  All the examples I have found restrict the openquery select (ASEls in my case) to a single row.

  I want to update all the rows that have changed since the last update.

  update openquery(ASEls,'select emp_id, emp_name, emp_status' from myasedb..emp '

  set emp_name = ms.dbo.emp_name,

  emp_status = ms.dbo.emp_status

  from ms.dbo.emp_status

  where ms.dbo.emp_id = ???????????????

  I'm stuck here. I do not know how the join works with openquery.

  I workaround will be to create a emp_update table on ASE, insert to it, let an ASE insert trigger update the emp table, but I hoped to do it with openquery.

  Thanks

• Rich Cowan

  Old Hand

  Points: 336

  More actions

  July 25, 2006 at 11:10 am

  #651420

  In my experience linking to a MySQL database, you simply surround your OPENQUERY statement in parenthesis and place it into your join.

  SELECT a.*, b.* FROM tbl1 INNER JOIN (SELECT * FROM OPENQUERY([LinkedServerName],'SELECT * FROM [tblname];') b

  I know that this works for MySQL from SQL.  Not too sure about Sybase.

  R.