We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL update statement addition?

Medium Priority
449 Views
Last Modified: 2012-05-11
I need to perform an UPDATE statement in SQL.  I need to take the "Amount" field and add to it the amount from a parameter when the fields "Field1" and "Field2" are equal.  For example, if the database already contains an entry

Field1         Field2               Amount
Sport          Basketball        10000

And I want to add a record that is

Field1         Field2            Amount
Sport          Basketball     20000

The SQL statement should recognize that I am trying to insert a duplicate value for Field1 and Field2 and should UPDATE the current record with the sum of the two values, which would be 30000.  So the updated record would appear as

Field1       Field2            Amount
Sport        Basketball    30000
Comment
Watch Question

CERTIFIED EXPERT

Commented:
declare @amount money, @field1 varchar(80), @field2 varchar(80)
select  @amount = 20000, @field1 = 'Sport', @field2 = 'Basketball'

update myTable
set Amount = Amount + @amount
where Field1 = @field1
  and Field2 = @field2

CERTIFIED EXPERT

Commented:
If you then want to INSERT the row (if it does not exist) add this line IMMEDIATELY after the update statement:


if @@ROWCOUNT = 0  insert into myTable values(Field1,Field2,Amount) (@amount,@field1,@field2)
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
IF EXISTS (Select 1 from Table where Field1 = 'Sport' and Field2 = 'Basketball')
Update Table Set Amount = Amount + @Parameter where Field1 = 'Sport' and Field2 = 'Basketball'
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
I think you need a insert statement. Please try the below script.

Note:- Please run this only once

INSERT INTO your_table
SELECT
      DISTINCT
                   Field1
                  ,Field2
                  ,SUM(Amount)
      FROM your_table
GROUP BY Field1,Field2.
INstead of doing the IF statement, you can also just do an INSERT like the following.  If the parameters identify an existing row, then the INSERT will not have any work to do.  If the parameters don't identify an existing row, then the UPDATE won't have anything to do.

Note: The parameter order got corrected but a key word was missing (VALUES)
INSERT
INTO myTable VALUES
    (
        Field1
      ,Field2
      ,Amount
    )
VALUES
    (@field1,
     @field2,
     @amount)

Open in new window

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.