0

So I have two tables. Say one table has a list of students, a student ID for each student, and a home address for each student.

Then you have another table that has a subset of the students in the first able, (and they are in a completely different order) with updated addresses and a student ID.

I need a query that can match the student ID of the two tables, and thereby update the address from the first table, using what is in the second table.

This is the query I tried, but no luck: 

UPDATE Roster, UpdatedRoster
SET Roster.Address = (SELECT Address FROM UpdatedRoster WHERE Roster.StudentID = UpdatedRoster.StudentiD)         
WHERE Roster.StudentID =  UpdatedRoster.StudentiD

Any help here would be greatly appreciated.

Update: This is on Microsoft Access FWIW.

SQLChao
  • 7,709
  • 1
  • 17
  • 32
user3451298
  • 11
  • 1
  • 4
  • 1
    I took your subject here, put it into the search feature (top right) and found many questions like this already answered. Such as http://stackoverflow.com/questions/13625805/updating-a-table-with-a-column-from-another-table-in-sql – Twelfth Jul 31 '14 at 19:28
  • possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) – Twelfth Jul 31 '14 at 19:29
  • The example in your link is similar, but not what I'm looking for. – user3451298 Jul 31 '14 at 19:33

1 Answers1

0
UPDATE Roster
SET Roster.Address = UpdatedRoster.Address
FROM Roster, UpdatedRoster         
WHERE Roster.StudentID =  UpdatedRoster.StudentiD
Khanjan
  • 183
  • 8
  • (taken from marc_s's [comment](http://stackoverflow.com/questions/25066218/sql-trigger-inserting-from-multiple-tables#comment38995276_25066218)): [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced by the *proper* ANSI JOIN syntax with the ANSI-**92** SQL Standard (more than **20 years** ago) – Matthew Haugen Jul 31 '14 at 20:55
  • In other words: not a bad answer, but it is outdated. – Matthew Haugen Jul 31 '14 at 20:57
  • I keep getting a missing query operator error when I try this. – user3451298 Aug 01 '14 at 13:26