write a Update Statement in single Line for...

Question

0.00/5 (No votes)

See more:

Write a Update Statement in single Line for...

ID Name Sal
1 Ram 1000------>Update +5%
2 Shyam 2000------>Update -10%
3 John 3000------>Update +15%
4 Smith 4000------>Update -20%

Posted 1-Aug-13 0:47am

RajeevSingh.net

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 1-Aug-13 6:53am

What have you tried?

Adarsh chauhan 1-Aug-13 7:03am

what have you tried and where you stuck.
sorry boss but we are here to help each-other, not to do assignments .

2 solutions

Solution 2

Try this script:

SQL

DECLARE @tmp TABLE (ID INT IDENTITY(1,1), [Name] VARCHAR(30), Sal INT)
 
INSERT INTO @tmp ([Name], Sal)
SELECT 'Ram', 1000 ------>Update +5%
UNION ALL SELECT 'Shyam', 2000 ------>Update -10%
UNION ALL SELECT 'John', 3000 ------>Update +15%
UNION ALL SELECT 'Smith', 4000 ------>Update -20%

UPDATE t1
	SET t1.Sal = t2.NewSal
FROM @tmp AS t1 INNER JOIN (
	SELECT ID, [Name], Sal, CASE WHEN (RowNo % 2) = 0 THEN Sal+(Sal/100*Inc*RowNo*-1) ELSE Sal+(Sal/100*Inc*RowNo) END AS NewSal
	FROM (
		SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNo, ID, [Name], Sal, 5 AS Inc
		FROM @tmp
		) AS Src
		) AS t2 ON t1.ID = t2.ID

SELECT *
FROM @tmp

Result:

ID      Name    Sal
------------------------------
1	Ram	1050
2	Shyam	1800
3	John	3450
4	Smith	3200

Note: I used ROW_NUMBER() function to avoid breaks in ID field

Posted 1-Aug-13 1:27am

Maciej Los

Updated 1-Aug-13 1:44am

v2

Comments

Raja Sekhar S 1-Aug-13 7:37am

Nice One Maciej Los... I was Updating my Solution with Modulo Operator and Saw your Solution...
I have a Doubt Maciej Los.. If i Use the Update Statement which i wrote it works But if i change this
Salary + ( Salary *Id* 0.05 ) to
Salary + ( Salary *Id* (5/100) ) the Update Statement Won't Work,
If i Use Salary + ( (Salary/100) *Id * 5 ) ) again it Works.. Why..?

Maciej Los 1-Aug-13 7:46am

Remove brackets around 5/100 and test it again... Let's see what would happen.

Raja Sekhar S 1-Aug-13 7:49am

yes... It Worked What's Wrong With the Brackets..?
+5!

Maciej Los 1-Aug-13 7:53am

You need to self-answer it ;) Just think about it.
Thank you for voting 5 ;)

Raja Sekhar S 1-Aug-13 7:56am

Struggling with it since i posted...

Maciej Los 1-Aug-13 8:01am

[EDIT]
I won't explain... i use example:


SELECT CONVERT(DECIMAL(4,2),5)/100, CONVERT(INT,5)/100

Better example ;)

Raja Sekhar S 1-Aug-13 8:09am

Got it.. Thank You...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Accepted Answer · 2013-08-01T01:00:00

Solution 1

SQL

Create Table #Temp (Id Int,Name Nvarchar(40),Salary Real)
Insert into #Temp 
Select 1,'Ram',1000
Union All Select 2,'Shyam',2000
Union all Select 3,'John',3000
Union All Select 4,'Smith',4000

Update #Temp Set Salary= Case When Id = 1 Then Salary+(Salary*0.05)
			      When Id = 2 Then Salary-(Salary*0.10)
			      When Id = 3 Then Salary+(Salary*0.15)
			      When Id = 4 Then Salary-(Salary*0.20) End
Select * from #Temp
Drop Table #Temp

If you are trying to add id * 5% of salary for Odd Numbers and Decrease Id*10% for even Numbers Then the Query Should be Like This...

SQL

Update #Temp Set Salary= Case When Id % 2 <> 0  Then Salary + ( Salary *Id* 0.05 )
                              Else Salary - ( Salary * Id * 0.05 ) End

Posted 1-Aug-13 1:00am

Raja Sekhar S

Updated 1-Aug-13 1:32am

v5

Comments

Adarsh chauhan 1-Aug-13 7:05am

Good answer Raja. +5 for answer. but don't you think question seems like homework..
He should try at his own level and if he finds an error then he should ask for solution.

Raja Sekhar S 1-Aug-13 7:11am

Thanks Adarsh... It seems he was doing his part of work (See his Profile page)... So wrote the Solution...

Adarsh chauhan 1-Aug-13 7:15am

strange... This question doesn't suits him..
I am surprised after seeing his profile..

Maciej Los 1-Aug-13 7:57am

if you are trying to add id * 5% of salary for Odd Numbers and Decrease Id*10% for even Numbers Then the Query Should be Like This... - This is what you should see on the first look ;)
+5 for improved answer

Raja Sekhar S 1-Aug-13 8:00am

Thanks.....