SQL Syntax For Copying Field Values From One Table To Another 1

[JCruz063]

Hello all...

I have a field in a child table which belongs to a parent table. I'm going to add that field to the parent table and I'll remove it from the child table.

Now, data entry started a while back and there are a few hundred child records already. I want to copy the existing values of the field in question from the child table to the parent table.

Here's the layout:

ParentTable:
parentKeyField
...
fieldX (just added, has no data)

ChildTable:
childKeyField
parentKeyField
...
fieldX (to be removed; should have been in parentTable from the get go, but has data now)
-----------------------

I would like to do this with an Update Query which looks something along the lines of:

UPDATE ParentTable 
SET ParentTable.fieldX = ChildTable.fieldX
WHERE ParentTable.parentKeyField = ChildTable.parentKeyField

But the above obviously does not work because I'm missing something.

Can anyone shed some light?

Thanks

_________________________________
I think, therefore I am. [Rene Descartes]

 

[PHV]

Like this ?

UPDATE ParentTable INNER JOIN ChildTable ON ParentTable.parentKeyField = ChildTable.parentKeyField
SET ParentTable.fieldX = ChildTable.fieldX
WHERE ParentTable.fieldX Is Null AND ChildTable.fieldX Is Not Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JCruz063]

Beautiful! I'm going to give it a shot and report back.

Thanks.

_________________________________
I think, therefore I am. [Rene Descartes]

 

[JCruz063]

Works like a champ! Thanks.

I didn't know you could do Updates on JOINs like that. I knew I had to join the tables but I didn't even try updating using a JOIN because I just assumed it wouldn't work. Not sure why — because it's Friday I guess

Thanks again.

_________________________________
I think, therefore I am. [Rene Descartes]