4 Replies Latest reply: Feb 26, 2013 2:54 PM by Dan Bracuk RSS

    Updating table with query of 2 other tables?

    sakonnetweb Community Member

      Greetings

       

      I apologize in advance if this seems too easy to do- but I am missing something.

       

      I have 8000 records in an Access DB (I know- it's going into SQL soon) in which all  the departmental office IDs need to be updated.

       

      The column in the main table  is named "q_office_ID".

       

      The user table includes  user_ID &  office_ID.

       

      The office table also includes office_ID.

       

      This UPDATE sets all the values to the number 10?:

       

      <cfquery name="update" datasource="#Request.BaseDSN#">

       

      SELECT     queue_ID, q_user_ID, user_ID, office_ID

       

      FROM     main_helpdesk, lookup_user

      WHERE  q_user_ID = user_ID

       

      </cfquery>

       

       

       

      <cfloop query="update"><cfquery name="updatedata" datasource="#Request.BaseDSN#">

      UPDATE main_helpdesk

       

       

      SET q_office_ID = #office_ID#

       

       

      </cfquery></cfloop>

       

      I know there is something obviously wrong with the update- any help would be greatly appreciated.

       

      Thank You