SQL update statement addition?
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
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
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,Amoun
if @@ROWCOUNT = 0 insert into myTable values(Field1,Field2,Amoun
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IF EXISTS (Select 1 from Table where Field1 = 'Sport' and Field2 = 'Basketball')
Update Table Set Amount = Amount + @Parameter where Field1 = 'Sport' and Field2 = 'Basketball'
Update Table Set Amount = Amount + @Parameter where Field1 = 'Sport' and Field2 = 'Basketball'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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)
Note: The parameter order got corrected but a key word was missing (VALUES)
INSERT
INTO myTable VALUES
(
Field1
,Field2
,Amount
)
VALUES
(@field1,
@field2,
@amount)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select @amount = 20000, @field1 = 'Sport', @field2 = 'Basketball'
update myTable
set Amount = Amount + @amount
where Field1 = @field1
and Field2 = @field2