Populate one table with values from another

[Sean]

I have two linked tables in my db. I need to pull all the values from
tableA and insert into tableB where tableA.id = tableB.id

Here's my SQL query, which isn't working:

INSERT INTO tblA
SELECT insthick FROM tblB
WHERE tblA.COMP_ID = tblB.COMP_ID

Does anyone have any suggestions?

Thanks

 

[MGFoster]

I have two linked tables in my db. I need to pull all the values from
tableA and insert into tableB where tableA.id = tableB.id

Here's my SQL query, which isn't working:

INSERT INTO tblA
SELECT insthick FROM tblB
WHERE tblA.COMP_ID = tblB.COMP_ID

Does anyone have any suggestions?

INSERT INTO tblA (insthick)
SELECT B.insthick
FROM tblB As B INNER JOIN tblA As A
ON A.COMP_ID = B.COMP_ID

 

[Amy Blankenship]

If you already have a record, which you would have to for tblA.comp_id to be
equal to tbleB.comp_id, then you need to be using update instead of insert.
If, instead, you want to create new records, you want

INSERT INTO tblA (insthick, comp_ID)
SELECT insthick, comp_ID
FROM tblB

HTH;

Amy

 

[Sean]

Thanks Amy...unfortunately I'm having trouble with the Update query:

UPDATE tblPiping1
SET insthick = t.insthick
FROM tblPiping t, tblPiping1 s
WHERE t.comp_id = s.comp_id;

Can you tell me whats wrong with this query? I've never used variables
in a query (tblPiping t) so I'm probably doing something wrong there.

Thanks

 

[Amy Blankenship]

Try

UPDATE tblpiping1 s
SET s.insthick=
(SELECT t.Insthick
FROM tblPiping t
WHERE t.comp_ID = s.comp_id);

HTH;

Amy

 

[Sean]

I get an "Operation must use an updateable query" error.

Does it matter that the tables are linked? I don't think this should
be an issue.

Sean

 

[John Spencer (MVP)]

Pardon me. You might try the following.


UPDATE tblPiping1 A INNER JOIN tblPiping S

 

[John Vinson]

I get an "Operation must use an updateable query" error.

Is t.comp_id the Primary Key or otherwise a unique index in
tblPiping1?

UPDATE tblPiping1
SET insthick = t.insthick
FROM tblPiping t, tblPiping1 s
WHERE t.comp_id = s.comp_id;

Try the SQL/92 join rather than the older WHERE clause join:

UPDATE tblPiping NNIER JOIN tblPiping1
ON tblPiping.comp_id = tblPiping1.comp_id
SET tblPiping1.insthick = tblpiping.insthick;

This will still fail if you don't have a unique index, since Access
cannot tell which (of possibly many) records for a given comp_id
should be updated.

John W. Vinson[MVP]

 

[Sean]

Thanks John, that worked perfectly.