Need help with updating columns

[Slow Learner]

Hi, I have two int columns in a table that have a sample data as below and I need to update these columns with 1).If ColA has a non negative number then update ColB with 0 (zero). 2).If ColA has a negative number then update ColB with the same value that ColA has. Can some please help me with sql? Thanks in advance. L ------------------------- ColA | ColB -----------|------------ -1 -1 -1 -1 -3 -1 4 -1 -1 -1 7 -1 3 -1 73 -1 -3 -1 39 -1 32 -1 35 -1 3 -1 31 -1 3 -1 397 -1 400 -1 403 -1 406 -1 -1 -1 412 -1 436 -1 421 -1 421 -1 424 -1 -1 -1 430 -1 433 -1 -1 -1 415 -1 442 -1 445 -1 -1 -1 -3 -1 487 -1 523 -1 568 -1 472 -1 475 -1 478 -1 -3 -1 490 -1 493 -1 496 -1 502 -1 505 -1 511 -1 514 -1 517 -1 520 -1 -1 -1 526 -1 538 -1 541 -1 544 -1 556 -1 559 -1 550 -1 457 -1 574 -1 -1 -1 580 -1 583 -1 586 -1 589 -1 595 -1 625 -1 604 -1 625 -1 610 -1 -1 -1 622 -1 625 -1 -1 -1 -1 -1 637 -1 -1 -1 649 -1 652 -1 655 -1 658 -1 661 -1 664 -1 667 -1 670 -1 673 -1 -1 -1 685 -1 688 -1 691 -1 697 -1 700 -1 703 -1 709 -1 712 -1 715 -1 718 -1 721 -1 -1 -1 -3 -1 -1 -1 -1 -1 -1 -1 -1 -1

[David Mujica]

I would use two Update statements. Begin Transaction update MyTable set ColB = 0 where ColA > 0 update MyTable set ColB = ColA where ColA < 0 By using a transaction you can review the number of rows affected and decide whether you want to Commit or Rollback the transaction. ><

[Wendelius]

One version is:

UPDATE TableName
SET ColB = CASE
WHEN ColA < 0 THEN 0
ELSE ColA
END;

Correction based on the good feedback by musefan:

UPDATE TableName
SET ColB = CASE
WHEN ColA >= 0 THEN 0
ELSE ColA
END

The need to optimize rises from a bad design.My articles[^]

modified on Wednesday, March 23, 2011 12:16 PM

[musefan]

What if ColA == 0?

I may or may not be responsible for my own actions

[musefan]

I think you misread the requirement (even thou you got an accepted answer), ColB should be 0 for NON-negative numbers

I may or may not be responsible for my own actions

[Wendelius]

You're right for non-negative it's vice versa. I think the OP still got the idea. :)

The need to optimize rises from a bad design.My articles[^]

[Klaus Werner Konrad]

UPDATE table SET colB = DECODE( SIGN( colA ), -1, colA, 0 )