Merge Tables

[cfobare79]

I tried the code below and received the error code: validation rule violation. Those to fields are set to the same data type.

INSERT INTO DaveWelchTable (AgencyID)
SELECT AgencyList.AgencyID


FROM AgencyList
INNER JOIN DaveWelchTable
ON AgencyList.AgencyID = DaveWelchTable.AgencyID

[June7]

DaveWelchTable is the imported dataset? Why would you be inserting records into DaveWelchTable? Don't you need to insert DaveWelchTable records into another table? And if the imported data does not have AgencyID, how can you equate the fields?

If you want to retain DaveWelchTable permanently in the db, then you need to do an UPDATE on its AgencyID field.

UPDATE DaveWelchTable SET DaveWelchTable.AgencyID = AgencyList.AgencyID FROM DaveWelchTable, AgencyList WHERE DaveWelchTable.AgencyName = AgencyList.AgencyName

Validation rule violation does not pertain to data type mismatch error.

[cfobare79]

What I want to do is take AgencyID from AgencyList (AgencyList.AgencyID) by matching AgencyName from both DaveWelchTable and AgencyList and have it inserted into DaveWelchTable. I was a bit confused with the questions from the last post and that SQL didn't work.

[June7]

What does 'did not work' mean - error message, wrong results, nothing happened?

Look closely at your INSERT statement and try to see the illogic of that approach. What you now describe is an UPDATE action. The suggested statement has correct syntax.

[cfobare79]

This is exactly what appears when I use the code as given.

[June7]

Bing: update field with data from another table

http://stackoverflow.com/questions/4...r-table-in-sql

I don't see anything wrong with the statement.

An alternative SQL to try:

UPDATE DaveWelchTable INNER JOIN AgencyList ON DaveWelchTable.AgencyName = AgencyList.AgencyName SET DaveWelchTable.AgencyID = AgencyList.AgencyID

Perhaps you should use the query builder and let it build the SQL statement instead of just pasting into the SQL window.

I seldom run INSERT or UPDATE that involves two tables.

[cfobare79]

worked perfectly that time... You are AWESOME... Have a wonderful weekend.