Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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
0
melli111
Asked:
melli111
3 Solutions
 
knightEknightCommented:
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

0
 
knightEknightCommented:
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)
0
 
knightEknightCommented:
oops!  I got the parameters in the wrong order:

if @@ROWCOUNT = 0  insert into myTable values(Field1,Field2,Amount) (@field1,@field2,@amount)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Alpesh PatelAssistant ConsultantCommented:
IF EXISTS (Select 1 from Table where Field1 = 'Sport' and Field2 = 'Basketball')
Update Table Set Amount = Amount + @Parameter where Field1 = 'Sport' and Field2 = 'Basketball'
0
 
knightEknightCommented:
FYI - the @@ROWCOUNT macro will contain the number of rows affected by the previous statement, so if the UPDATE query does not affect any rows, the last statement will do an insert instead.
0
 
Chandan_GowdaCommented:
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.
0
 
8080_DiverCommented:
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

0
 
ralmadaCommented:
it looks like you want an update if the record exists, and an insert if the record doesn't. If you're in SQL 2008 you can use the Merge statement, if not you will need one update and one insert. Check the code below:

--using merge
declare @field1 varchar(30)
declare @field2 varchar(30)
declare @Amount int

MERGE yourtable AS target
USING (SELECT @field1, @field2, @amount) AS source (field1, field2, amount)
ON (target.field1 = source.field1 and target.field2 = target.field2)
WHEN MATCHED THEN 
  UPDATE SET Amount = Amount + source.Amount
WHEN NOT MATCHED THEN	
  INSERT (field1, field2, Amount)
  VALUES (source.field1, source.field2, source.amount)

-- one update and one insert
declare @field1 varchar(30)
declare @field2 varchar(30)
declare @Amount int

	update a
	set a.amount = a.amount + b.amount
	from yourtable a
	inner join (select @field1 as field1, @field2 as field2, @amount as amount) b on a.field1 = b.field1 and a.field2 = b.field2


	insert yourtable
	select * from (select @field1 as field1, @field2 as field2, @amount as amount) a
	left join yourtable b on a.field1 = b.field1 and a.field2 = b.field2
	where b.field1 is null

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now