IF true Update -- help

[inarobis]

Hello,

I have two identical tables (same structure) but one table is more up to date of the other one.

I would like to tableA.date = tableB.date update the tableB.value if the tableA.value <> tableB.value for tableA.date = tableB.date

So I would like to do if it is possible :


if ( (tableA.value from tableA where tableA.date= '2005-06-30' and tableA.code = 12 and tableA.country = 'US' and tableA.ST = 'NY') = (select tableB.value from tableB where table.date = '2005-06-30' and tableB.code= 1303 and tableB.Code = 'NY ' and tableB.Country = 'US'))

print 'not updated'

else

UPDATE tableB
SET tableB.value = (
select value
from tableA
where tableA.Country = 'US' and tableA.code = 12 and tableA.ST = 'NY' )

WHERE tableB= 12 and tableB.ST = 'NY' and Country = 'US'

My question how to iterate this operation.
With a cursor? or a while? I would like to update all values in tableB which are different from values in table B for the same date.

Any suggestions,

Ina

 

[gmmastros]

Why not just update them all (if they are the same, then you'll never notice a difference)?

You should make a backup copy of the database before doing this.

You should test this to make sure the correct data is getting updated

[green]--UPDATE tableB
--SET    tableB.value = tableA.Value[/green]
Select TableA.Value, 
       TableB.Value, 
       TableA.Country,
       TableA.Code,
       TableA.State
From   TableB
       Inner Join TableA
         On  tableA.Country = 'US' 
         and tableA.code = 12 
         and tableA.ST = 'NY'

If the select returns the correct records, then simply remove the select part and uncomment the update/set part.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

That's Wrong. Sorry.

[green]--UPDATE tableB
--SET    tableB.value = tableA.Value[/green]
Select TableA.Value, 
       TableB.Value, 
       TableA.Country,
       TableA.Code,
       TableA.State
From   TableB
       Inner Join TableA
         On  TableA.Country = TableB.Country
         And TableA.Code = TableB.Code
         And TableA.State = TableB.State
Where  tableA.Country = 'US' 
         and tableA.code = 12 
         and tableA.ST = 'NY'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[inarobis]

Thanks George for this suggestion Good I will try with that

but Do you thing it is good way if I would like to update value in a big big table.

In that way I need to update them all even if the value in both table is the same.

Ina

 

[gmmastros]

I was thinking about that, too. You could try changing the from clause to...

From   TableB
       Inner Join TableA
         On  TableA.Country = TableB.Country
         And TableA.Code = TableB.Code
         And TableA.State = TableB.State
         [!]And TableA.Value <> TableB.Value[/!]

The problem you will have with this 'addition' will occur when 1 of the tables has a NULL in the value column. This addition to the code would prevent that value from getting updated because NULL does not equal another value AND NULL does not NOT equal a value either.

ex:

Declare @A int
Declare @B int

Set @A = 1
Set @B = NULL

If @A = @B 
	Select 'equal' 


If @A <> @B
	Select 'Not Equal'

Ordinarily, you would think that one or the other would be displayed, but since @B is NULL, neither If's compare to true.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[inarobis]

Hello George,

Thank you a lot for this help, really help. Now I am working on it. Thank you once again

Ina