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
 General SQL Server Forums
 New to SQL Server Programming
 update by 5%

Author  Topic 

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 06:34:37
How do you update prices by 5%

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 06:44:09
do you mean increment prices by 5 %? if yes,

UPDATE Table
Price = Price + (Price * .05)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-07-12 : 06:46:58
price=price*1.05
Go to Top of Page

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 06:50:35
Tried that and this error came back at me... Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-07-12 : 06:54:39
lets say your primarykey column is called id, then use this:

update table
set price=price*1.05
where id>0
Go to Top of Page

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 06:59:22
still got back the same error...
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-07-12 : 07:05:37
Please post the table design.

ps. I have never come across this error using mssql, only when using mysql.
Go to Top of Page

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 07:11:36
roomNo = Int(11)
hotelNo = Int(11)
type = varchar(45)
price = varchar(45)
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-07-12 : 07:16:04
I'm guessing the primary key is roomNo and hotelNo. Is this correct?
If so, try this:

update table
set price=price*1.05
where roomNo>0
and hotelNo>0
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-07-12 : 07:18:02
woops!
Is Price field varchar?
You cannot calculate on alphanumeric fields.
Go to Top of Page

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 07:23:00
That was grand all of them changes worked...
Go to Top of Page
   

- Advertisement -