How to update table in one single sql query..

[Guest_imported]

I have TableA(Ofc#,ACct#,Sales) and
TableB(Ofc#,ACct#,Revenue).

I want to set the Sales column of Table A equal to Revenue column of Table B where ever Ofc# Acct# pair matches between two tables. If there is no match, I want to retain the original value of Sales.

I could not do it in one SINGLE sql query.

Help Please,

Cheers,
Sree. [sig][/sig]

 

[THoey]

Try this...

UPDATE TableA (Sales)
SET TableA.Sales = TableB.Revenue
WHERE TableA.OFC# = TableB.OFC# AND
      TableB.Acct# = TableB.Acct#

I didn't test this, so you might want to make sure you can rollback if it doesn't do the trick. That or try it on a test copy of the tables.

Hope it helps...
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[Guest_imported]

I think Update and Join never goes together( atleast in DB2 ), unless we use a sub-query. But, I can't use a sub-query in this situation.

Kind of deadlock

I tried using a query similar to the one you have suggested . Pch.. it didn't work.

Thanks for the efforts anyway,
Sree.
[sig][/sig]

 

[bigscouse]

See post by Joep, last update 13/9/2000, headed: -
&quot;2 tables INSERT/UPDATE at the same time?&quot; [sig]<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>Top man[/sig]