0

I have this update statement - 

<cfquery name="updateqry" datasource="thisone">
    update dbOne.tableA a
    inner join dbTwo.tableB b on a.docid=b.docid
    set a.download_dt = now(), a.document_status = "DOWNLOADED", a.rec_mod_dt = now()
    where b.school_id = <cfqueryparam value="#project.school_id#">
    and b.project_id = <cfqueryparam value="#arguments.project_id#">
    and date(b.doc_add_dt) >= <cfqueryparam value="#arguments.begin_dt#">
    and date(b.doc_add_dt) <= <cfqueryparam value="#arguments.end_dt#">
    and a.download_dt is null and a.document_status <> "DOWNLOADED"
</cfquery>

dbOne and dbTwo are on different servers now. How do I separate this update statement into multiple statements in order to get the info I need from each separate server's tables? I've never seen this update inner join syntax before, I'm really stumped.

Robyn Paxton
  • 800
  • 1
  • 6
  • 21
  • 3
    read this. https://stackoverflow.com/questions/5370970/how-to-create-linked-server-mysql. Set up the necessary linked servers and use them. – Dan Bracuk Aug 08 '17 at 16:23
  • Set up trusted links between the servers. Check Dan's link. – Shawn Aug 08 '17 at 16:27
  • assuming I am not the dba and I can't set up the linked server solution, does that make it absolutely impossible to execute a solution? – Robyn Paxton Aug 08 '17 at 16:30
  • Get the dba to do it for you. It's not the only solution, but it's the best one. – Dan Bracuk Aug 08 '17 at 16:40
  • would another solution be to select what I need from tableB and loop through the result set and do an update on tableA with each result? – Robyn Paxton Aug 08 '17 at 17:00
  • 1
    That would work, and it wouldn't be the end of the world for a few dozen records. However, the more records you have, the worse this option becomes. – Dan Bracuk Aug 08 '17 at 19:02

0 Answers0