SQL expert needed - update TableA from TableB in access

[Ray]

Hello,

This one I think should be easy when you now how but I couldn't get it
today.
I have to run a kind of double check routine.
1 database, 2 tables. No linking or referential integrity or any of that.


TableA has 2 fields that matter - [CoilNumber] [MaterialType].... [and
others]
TableB is exactly the same - [CoilNumber] [MaterialType].... [and
others]

In TableA both fields have values
In TableB only [CoilNumber] has a value.

I need two things to happen here, and here is the kind of pseudo code I'm
working from to try and explain it.


UPDATE
TableB[MaterialType] = TableA[MaterialType]
WHERE
TableB[MaterialType] = "" AND TableB[CoilNumber] =
TableA[CoilNumber]

Stage 1: Make the two tables the same
Stage 2: If the the field in TableB already has a value then pass up the
value and throw an error


Don't worry about why it is this way, and the talk about data duplication
etc. I'm just doing what I'm told.

Any help appreciated.

Ray.

[Stanislav]

Re: SQL expert needed - update TableA from TableB in access

This is the SQL you can use in a query:

UPDATE TableB INNER JOIN TableA ON [TableB].CoilNumber =
TableA.CoilNumb er SET [TableB].MaterialType = [TableA].[MaterialType]

That's all... Good Luck!

[Ray]

Re: SQL expert needed - update TableA from TableB in access

Where is the bit where we only update rows in TableB that = "" ?
Or will the UPDATE only affect rows with differences?


"Stanislav" <BestS@abv.bg > wrote in message
news:38b912ef.0 403040037.63574 345@posting.goo gle.com...[color=blue]
> This is the SQL you can use in a query:
>
> UPDATE TableB INNER JOIN TableA ON [TableB].CoilNumber =
> TableA.CoilNumb er SET [TableB].MaterialType = [TableA].[MaterialType]
>
> That's all... Good Luck![/color]

[Magic]

Re: SQL expert needed - update TableA from TableB in access

for stage 2: get a list of all numbers where the materialtype is not
null.
SELECT Table2.CoilNumb er, Table2.Material Type
FROM Table2
WHERE (((Table2.Mater ialType) Is Not Null));

for stage 1:
make an update query like:
UPDATE Table1 INNER JOIN Table2 ON Table1.CoilNumb er =
Table2.CoilNumb er SET Table2.Material Type = [Table1].[materialtype];

If you want to make that more safe then update only the fields in
table2 where the marialtype is null:

UPDATE Table1 INNER JOIN Table2 ON Table1.CoilNumb er =
Table2.CoilNumb er SET Table2.Material Type = [Table1].[materialtype]
WHERE (((Table2.Mater ialType) Is Null));

[Ray]

Re: SQL expert needed - update TableA from TableB in access

Thank you for the help. I did get a grip on the UPDATE command but in the
end it seems to have been unsuitable.
I needed to update tableB from A but if there was an entry already in B I
needed to catch that error so I just did it on
the VB end instead.

Thanks again for the time.

Ray.