CFQUERY - Missing Operator

Can anyone help me out with this statement. I am trying to insert data into table a from table b where table a and table b have three fields which are the same, and I keep getting a missing operator error. Is this even possible, because when I was searching on this topic I noticed a lot of people discussing the use of a temp table. I am using MS access and CF 6.1MX, I tried to run this in the SQL view query in access and got the same error. I posted this on the SQL forum, but wanted to post here as well to see if anyone had come across anything like this before. Thanks in advance.

<cfquery name="updatetest" datasource="ptnadmin">
Update test
SET officeaddress = b.address,
officeaddress2 = b.address2,
officecity = b.city,
officestate = b.state,
officezip = b.zip,
officephone = b.phone,
me = b.me,
ims = b.ims
FROM test a
INNER JOIN A751P b on b.firstname = a.firstname AND b.lastname = a.lastname AND b.state = a.state
</cfquery>

Your query is wrong.

UPDATE [table] SET [field]=[value] WHERE [field]=[value]
or:
UPDATE [table1] SET [field]=(SELECT [field] FROM [table2] WHERE [field]=[value]) WHERE [field]=[value]

Read over some SQL tutorials on subqueries.

But that would only update one field. I need to update multiple fields.

I need to take info from table1.a and put it into table2.a, take info from table1.b and put it into table2.b, if table1.c and table2.c are equal.

Are you inserting data or are you updating it, i.e. - are there already records in the table that you wish to update?

If not, and you are indeed inserting it, then this is the syntax:
INSERT INTO table1 (field1,field2,field3)
(SELECT field1,field2,field3 FROM table2)

This generally works with all SQL versions.
It is very important that both lists of fields correspond to each other.

Hope that helps.

Ok so I updated it to this and got a syntax error, What am I missing here? Thanks for the help by the way

<cfquery name="updatepcptest" datasource="ptnadmin">
INSERT INTO PCPtest(officeaddress, officeaddress2, officecity, officestate,officezip, officephone, me, ims)
(SELECT address, address2, city, state, zip, phone, me, ims
FROM A751P)
WHERE A751P.firstname = PCPtest.firstname AND A751P.lastname = PCPtest.lastname AND A7514.state = PCPtest.state)
</cfquery>

You may want to double check on the second table information on your update--the 'FROM' and the 'INNER JOIN.'

Of course, if you can copy in your error, that may help with your question as well.

After reading up on this, I have the following in the CF query. I don't get any errors, however it sets new records into the database at the bottom of the table instead of inserting the values into the correct fields. I think this has something to do with appending, but am not sure. I don't want to insert new fields, but I want to insert the data from one table into the correct fields in another. Is this were I need to back to trying to update instead of insert, and if so how in the world. I am so confused.

INSERT INTO PCPtest ( officeaddress, officecity, officestate, officezip, officephone, me, ims )
SELECT address, city, state, zip, phone, me, ims
FROM A751P
WHERE exists (SELECT officeaddress, officecity, officestate, officezip, officephone, me, ims FROM PCPtest WHERE A751P.firstname = PCPtest.firstname AND A751P.lastname = PCPtest.lastname AND A751P.state = PCPtest.homestate)

Insert adds new fields to the table and update edits the information in a specific row determined by the where statement (i.e. where ID = 5 or where lastname = 'smith'). Since you are trying to edit/append data, you would probably need to use update.

Wouldn't this do the trick? Why am I getting a syntax error in the UPDATE statement?

UPDATE PCPtest
SET("officeaddress", "officecity", "officestate", "officezip", "officephone", "me", "ims") = ([A751P.address], [A751P.city], [A751P.state], [A751P.zip], [A751P.phone], [A751P.me], [A751P.ims])
WHERE exists (SELECT address, city, state, zip, phone, me, ims FROM A751P WHERE A751P.firstname = PCPtest.firstname AND A751P.lastname = PCPtest.lastname AND A751P.state = PCPtest.homestate);

ok so I figured out how to do this pretty easily for one field per query

UPDATE PCPtest, A751P SET PCPtest.me = A751P.me
WHERE A751P.firstname=PCPtest.firstname And A751P.lastname=PCPtest.lastname And A751P.state=PCPtest.homestate;

Anyone have any ideas on how to do this for multiple fields at the same time? Thanks for the help