replacing data within a table 1

[collierd]

Hello

I have a slight problem

I have a table (sanalysis) made up of:

sanalysisid varchar(255)
sanqty varchar(255)
sansalesval varchar(255)
sanweight varchar(255)

The data type is irrelevant as this will change at a later date

My intention is to increment daily records to this file for everything bar the weight (sanweight) from two imported csv files. I will initially set the weight to 0 when using insert into

That part works fine

I now have two tables (north_sanalysisfull and south_sanalysisfull) which comprise of

sanalysisid varchar(255)
sanweight varchar(255)

For the whole year to date (we have a data quality issue with weights and are forever correcting them)

All I want to do is replace sanweight on all sanalysis records with sanweight from north_sanalysisfull and south_sanalysisfull

Does anybody know what the easiest way to achieve this is ?

Thanks in advance

Damian.

 

[Guest_imported]

Why don't you write a trigger that updates the sanalysis data when the tables north_sanalysisfull or south_sanalysisfull are updated?

 

[angiole]

If sanalysis ID is a unique PK on the first table, then 2 update steps from the north and south files.

Of course, if the sanalsys ID from the north and south files are repeating, then you risk overwriting older info.

I'm guessing there may be a date involved here somewhere...
AA 8~)

 

[collierd]

Thanks

Is this the most efficient update process

update sanalysis
set sanweight = sf.sanweight
from sanalysis sa
left outer join nsanalysisfull sf on sa.sanalysisid = sf.sanalysisid

Damian.

 

[angiole]

You don't need to specify left outer join since all your data is already in the driving table.

You may need an alias for table sanalysis so the set statement doesn't get confused.
AA 8~)

 

[collierd]

Dropped the left outer join
Used

from sanlaysis sa, nsanalysisfull sf
where sa.sanalysisid = sf.sanalysisid

set sanweight = sf.sanweight
needs to remain the same as aliases cannot be used with set
(or at least help on update suggests that)

This seems to have worked fine

Thanks for all your help

Damian.