Find matching records in two tables and update 1

[nathanstevenson]

Hi there,

I have 2 tables, I want to find the matching records (based on 5 different fields) and then update the first table with the ID from the second WHERE the records match (i.e. into a foreign key field).

Any advice?

Nathan

 

[THoey]

You could do this through an UPDATE query:

UPDATE MyTable1
SET MyTable1.ThisField = MyTable2.ThatField
WHERE MyTable1.ThisField = MyTable2.ThatField AND
MyTable1.ThisField = MyTable2.ThatField AND
MyTable1.ThisField = MyTable2.ThatField AND
MyTable1.ThisField = MyTable2.ThatField AND
MyTable1.ThisField = MyTable2.ThatField;

You'll have to substsitue all the table and field names. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[tlbroadbent]

Update Table1 Inner Join Table2
On table1.key1=table2.key1 And table1.key2=table2.key2
And ...
Set table1.colname = table2.ID If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[THoey]

Nathan, the other Terry's way is probably best, I just hate writing joins. Not sure why. Too confusing for me to understand or something. I think both ways work as well... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[nathanstevenson]

Hey Thoey,

Okay, I have been mucking about with it, but isn't an update actually INSERT INTO?

I had:
INSERT INTO [Monthly Import].[Manager ID] FROM Manager.[Manager ID] WHERE Manager.[Manager Name] = Manager.[Manager Name] AND ... AND ...

Except it doesn't seem to work, I keep on getting syntax errors. I am also having conceptual difficulty understanding the actual execution of the code. For example,

Does it FIRST find all entries where Manager.Name = Monthly Import.Name (for eg), THEN take Manager.ID (from that record in Manager where the condition holds true, and then PUT that value INTO the Monthly Import.Manager ID field?

Also having a bit of difficulty with the syntax, is there any where I can go to read up on the exact syntax (use of brackets, parenthesis and punctuation for JET SQL)?

Could someone explain the logic.. it just has implications, like if you run a sub-query, does that execute FIRST, and if so, do you put the subquery after WHERE? And what logic purpose does EXISTS serve? I have read about it, but not quite sure... does it mean that "perform all above instructions if this subquery returns a record?" Or does it mean perform these instructions on only unqie records it returns, or perform these instructions on ALL instructions it returns?

I guess I'd probably have a better understanding if somebody just described the execution logic.

THanks!
Nathan

 

[tlbroadbent]

INSERT INTO creates a new row in the table if the row doesn't violate table constraints. UPDATE will modify an existing row in a table, also subject to constraints. INSERT INTO and UPDATE are not the same. However, an UPDATE query can INSERT INTO a table under certain conditions. This is true in Access Jet SQL but not in other versions of SQL.

The INSERT INTO query you posted is syntactically incorrect and logically not what you want to do. MS Access help contains a Microsoft JET SQL Reference. You should use that ebook to learn correct syntax. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm