Skip navigation
Currently Being Moderated

Updating table with query of 2 other tables?

Feb 26, 2013 7:07 AM

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

 
Replies
  • Currently Being Moderated
    Feb 26, 2013 9:42 AM   in reply to sakonnetweb

    The lack of a where clause is pretty significant.    

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 26, 2013 2:54 PM   in reply to sakonnetweb

    Did you ever try a single query?  This page describes how to do it.  http://www.techonthenet.com/access/queries/update2.php

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points