copy data from one table to another

[Bill - ESAI]

Hi All

I'm need to copy the Project Descriptions from the back up table
(Projects_Old) to my active table. I messed up and accidentally changed my
ProjectDescription field from a Memo to a 255 text which truncated all my
descriptions. Now I need to recover the ones I can from my back up. Both
table structures are identical and the ProjectID is the unique key in both.

This is my attempt so far

UPDATE Projects
SET Projects.ProjectDescription = (SELECT Projects_Old.ProjectDescription
FROM Projects, Projects_Old WHERE Projects.ProjectID=Projects_Old.ProjectID)
WHERE Projects.ProjectID = (SELECT Projects_Old.ProjectID FROM Projects,
Projects_Old WHERE Projects.ProjectID=Projects_Old.ProjectID);

Any pointers would be greatly appreciated.

Bill

 

[mscertified]

I think you last = needs to be IN e.g.

UPDATE Projects
SET Projects.ProjectDescription = (SELECT Projects_Old.ProjectDescription
FROM Projects, Projects_Old WHERE Projects.ProjectID=Projects_Old.ProjectID)
WHERE Projects.ProjectID IN (SELECT Projects_Old.ProjectID FROM Projects,
Projects_Old WHERE Projects.ProjectID=Projects_Old.ProjectID);

The way this is coded is likely to run very slow if your tables are big.

-Dorian

 

[Bill - ESAI]

would there be a fast, more efficient way of coding it??

Bill

 

[John W. Vinson]

Hi All

I'm need to copy the Project Descriptions from the back up table
(Projects_Old) to my active table. I messed up and accidentally changed my
ProjectDescription field from a Memo to a 255 text which truncated all my
descriptions. Now I need to recover the ones I can from my back up. Both
table structures are identical and the ProjectID is the unique key in both.

It's simpler than you are making it!

Just create a Query joining Projects to Projects_Old by ProjectID. Change it
to an Update query. Update Projects.[Project Description] to
Projects_Old.[Project Description].

The SQL would be

UPDATE Projects INNER JOIN Projects_Old
SET Projects.ProjectDescription = Projects_Old.ProjectDescription;

 

[Bill - ESAI]

That actually didn't work because the "ON" condition was left out but it
helped. Thank you very much.

Here's what did the trick

UPDATE Projects INNER JOIN Projects_Old ON
Projects.ProjectID=Projects_Old.ProjectID SET Projects.ProjectDescription =
Projects_Old.ProjectDescription;

You were obviously right, as usual I was making things harder then it had to
be.

Thanks again

Bill

Hi All

I'm need to copy the Project Descriptions from the back up table
(Projects_Old) to my active table. I messed up and accidentally changed my
ProjectDescription field from a Memo to a 255 text which truncated all my
descriptions. Now I need to recover the ones I can from my back up. Both
table structures are identical and the ProjectID is the unique key in
both.

It's simpler than you are making it!

Just create a Query joining Projects to Projects_Old by ProjectID. Change
it
to an Update query. Update Projects.[Project Description] to
Projects_Old.[Project Description].

The SQL would be

UPDATE Projects INNER JOIN Projects_Old
SET Projects.ProjectDescription = Projects_Old.ProjectDescription;