CODEWITHSUNDEEP
sqlms-access
Peter Jeong
Peter Jeong

Reputation: 15

INSERT INTO statement for matching row - MS Access SQL

I have 2 tables...

Table 1 looks like

ID / Name  / Amount / Number
=========================
1 / Peter / 100 /  

2 / David / 80 / 

3 / Steve /70 /

I would like to use 'INSERT INTO' function to fill these empty 'Number field' using Table 2 with a matching ID. [Updating existing Table 1]

ID / Number                          <- This is table 2
=========================
1 /  123

2 /  456

3 /  756

so the final table 1 should look like... 

ID / Name  / Amount / Number
=========================
1 / Peter / 100 / 123 

2 / David / 80 / 456

3 / Steve /70 / 756

Any ideas? Is it possible to use insert into function without creating a toally new table? Thanks

Upvotes: 0

Views: 239

Answers (2)

Glorfindel
Glorfindel

Reputation: 22631

I'm not sure if this works with the SQL dialect used in MS Access, but you could give it a try:

UPDATE table1 INNER JOIN table2 ON table1.ID = table2.ID
  SET table1.Number = table2.Number

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18737

You need to use UPDATE query, not INSERT:

UPDATE Table1 INNER JOIN 
       Table2 ON Table1.ID=Table2.ID
SET Table1.Number=Table2.Number

Upvotes: 1

Related Questions