Modifying an entire row data HELP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Modifying an entire row data HELP

Author

Topic

noonz
Starting Member

33 Posts

Posted - 2010-04-19 : 10:58:09

hello sqlteam!

i have a site that sells coins, lets say there is a column dedicated to prices

if i needed to adjust all of the prices by a percentage or amount, how would i do that?

say theres a column with 5 coins, i would need to modify it either by a $10 increase or a 25% increase for all of them

thank you for your help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-19 : 11:03:07

Can you give example data and wanted result?

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 11:05:08

If your table is called coins and it has a column called price then something like


UPDATE coins SET
    price = price * 125.0 / 100.0 -- 25% Increase
WHERE
    <restrictions here>

Or if you wanted to add a fixed amount then


UPDATE coins SET
    price = price + 10 -- 10 increase
WHERE
    <restrictions here>

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:07:07

you need to use separate parameters for each and do like


UPDATE Table
SET PriceCol=CASE WHEN @IncreaseAmt IS NOT NULL THEN PriceCol + @IncreaseAmt
                  WHEN @Percentage IS NOT NULL THEN PriceCol * (1+(@Percentage/100))
                  ELSE PriceCol
             END

here @IncreaseAmt and @Percentage will be parameters through which you pass amt or %

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-19 : 11:16:50

Aha - now I understand!
This was hard to understand for me: theres a column with 5 coins

No, you're never too old to Yak'n'Roll if you're too young to die.

noonz
Starting Member

33 Posts

Posted - 2010-04-19 : 11:17:50

Ooh very naise

Thanks guys!

visakh, how could i pass that? would it be saved as a SP? call up the SP w/parameters?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:25:05

quote:
Originally posted by noonz

Ooh very naise

Thanks guys!

visakh, how could i pass that? would it be saved as a SP? call up the SP w/parameters?

Yup. You could create a SP with two parameters and pass either of them a value at a time based on your reqmnt. the body will be like


CREATE PROC Yourprocnamehere
@IncreaseAmt decimal(10,2)=NULL,
@Percentage decimal(10,2)=NULL
AS

UPDATE Table
SET PriceCol=CASE WHEN @IncreaseAmt IS NOT NULL THEN PriceCol + @IncreaseAmt
                  WHEN @Percentage IS NOT NULL THEN PriceCol * (1+(@Percentage/100))
                  ELSE PriceCol
             END



GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

noonz
Starting Member

33 Posts

Posted - 2010-04-19 : 11:41:08

I made this:

CREATE PROC D2UpdatePrices
@IncreaseAmount decimal(10,2) = NULL
@IncreasePercent decimal(10,2) = NULL
AS
UPDATE dbo.SLProducts
SET D2ProductPrice = CASE
WHEN
@IncreaseAmount IS NOT NULL THEN D2ProductPrice + @IncreaseAmount
@IncreasePercent IS NOT NULL THEN D2ProductPrice + (1+(@IncreasePercent/100))
ELSE D2ProductPrice
END
GO

Received this:

Msg 102, Level 15, State 1, Procedure D2UpdatePrices, Line 3
Incorrect syntax near '@IncreasePercent'.
Msg 137, Level 15, State 2, Procedure D2UpdatePrices, Line 8
Must declare the scalar variable "@IncreaseAmount".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:44:29

quote:
Originally posted by noonz

I made this:

CREATE PROC D2UpdatePrices
@IncreaseAmount decimal(10,2) = NULL,
@IncreasePercent decimal(10,2) = NULL
AS
UPDATE dbo.SLProducts
SET D2ProductPrice = CASE
WHEN
@IncreaseAmount IS NOT NULL THEN D2ProductPrice + @IncreaseAmount
@IncreasePercent IS NOT NULL THEN D2ProductPrice + (1+(@IncreasePercent/100))
ELSE D2ProductPrice
END
GO

Received this:

Msg 102, Level 15, State 1, Procedure D2UpdatePrices, Line 3
Incorrect syntax near '@IncreasePercent'.
Msg 137, Level 15, State 2, Procedure D2UpdatePrices, Line 8
Must declare the scalar variable "@IncreaseAmount".

missed a ,

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

noonz
Starting Member

33 Posts

Posted - 2010-04-19 : 11:56:57

CREATE PROCEDURE D2UpdatePrices
@IncreaseAmount decimal(10,2) = NULL,
@IncreasePercent decimal(10,2) = NULL
AS
UPDATE dbo.SLProducts
SET D2ProductPrice = CASE
WHEN
@IncreaseAmount IS NOT NULL THEN D2ProductPrice + @IncreaseAmount
@IncreasePercent IS NOT NULL THEN D2ProductPrice + (1+(@IncreasePercent/100))
ELSE D2ProductPrice
END
GO

Msg 102, Level 15, State 1, Procedure D2UpdatePrices, Line 9
Incorrect syntax near '@IncreasePercent'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-19 : 11:59:22

UPDATE dbo.SLProducts
SET D2ProductPrice = CASE
WHEN @IncreaseAmount IS NOT NULL THEN D2ProductPrice + @IncreaseAmount
WHEN @IncreasePercent IS NOT NULL THEN D2ProductPrice + (1+(@IncreasePercent/100))
ELSE D2ProductPrice
END

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 12:06:08

Just make sure that @increasePercent isn't an integer!


DECLARE @incPer INT

SET @incPer = 25

SELECT 1 + (@incPer/100)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

noonz
Starting Member

33 Posts

Posted - 2010-04-19 : 12:13:20

I don't get it..

visakh, you here buddy?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-19 : 12:17:20

quote:
Originally posted by noonz

I don't get it..

visakh, you here buddy?

It is ok because your @increasePercent is decimal(10,2).
Have you seen my last correction?

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 12:18:10

try running this in a query analyser window:


DECLARE @a INT
DECLARE @b INT

SET @a = 5
SET @b = 10

SELECT @a / @b

What do you think the result will be? 0.5?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-19 : 12:20:17

quote:
Originally posted by webfred

quote:
Originally posted by noonz

I don't get it..

visakh, you here buddy?

It is ok because your @increasePercent is decimal(10,2).
Have you seen my last correction?

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

noonz
Starting Member

33 Posts

Posted - 2010-04-19 : 12:21:41

there we go, needed an additional WHEN statement before the @IncreasePercent

THANK YOU GUYS! <333

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-19 : 12:23:13

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

noonz
Starting Member

33 Posts

Posted - 2010-04-19 : 12:39:54

One additional question, SQL allows me to execute the stored procedure with this code:

DECLARE @RC int
DECLARE @IncreaseAmount decimal(10,2)
DECLARE @IncreasePercent decimal(10,2)

-- TODO: Set parameter values here.

EXECUTE @RC = [D2StoreFront].[dbo].[D2UpdatePrices]
@IncreaseAmount
,@IncreasePercent

i will add the SET line to make this code:

DECLARE @RC int
DECLARE @IncreaseAmount decimal(10,2)
DECLARE @IncreasePercent decimal(10,2)

-- TODO: Set parameter values here.

SET @IncreaseAmount = 1

EXECUTE @RC = [D2StoreFront].[dbo].[D2UpdatePrices]
@IncreaseAmount
,@IncreasePercent

How do I execute this for a particular row?

Say I wanted to add

WHERE
D2ProductName='Product 6'

So it will only update the fixed amount or percent where the D2ProductName is equal to what I tell it..

Sorry guys, just tasked with doing this for my brothers site

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 01:01:47

you need to add this to procedure with additional parameter to identify product you want to update


CREATE PROCEDURE D2UpdatePrices
@IncreaseAmount decimal(10,2) = NULL,
@IncreasePercent decimal(10,2) = NULL,
@ProductName varchar(100)=NULL
AS
UPDATE dbo.SLProducts
SET D2ProductPrice = CASE
WHEN @IncreaseAmount IS NOT NULL THEN D2ProductPrice + @IncreaseAmount
WHEN @IncreasePercent IS NOT NULL THEN D2ProductPrice + (1+(@IncreasePercent/100))
ELSE D2ProductPrice
END
WHERE (D2ProductName=@ProductName OR @ProductName IS NULL)
GO

then call it like

DECLARE @RC int
DECLARE @IncreaseAmount decimal(10,2)
DECLARE @IncreasePercent decimal(10,2)

-- TODO: Set parameter values here.

SET @IncreaseAmount = 1,@ProductName='Product 6',.. other values


EXECUTE @RC = [D2StoreFront].[dbo].[D2UpdatePrices] 
@IncreaseAmount
,@IncreasePercent

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources