Update query on one column based on negative

Question

0.00/5 (No votes)

See more:

Need to Update a column B on basis on Column A,
Condition - If Column A(data type-Float) is Negative then Put -1 Sign in Column B and vice versa.

using SQL Server 2014

What I have tried:

Case if update query but failed to extract negative from column A

OP Code from solution:

SQL

update v_table1 set ColumnB= case when ColumnA <0 then -1 when ColumnA >0 then 1 when ColumnA =0 then 0 else null end

Posted 16-Jul-18 19:48pm

Member 13518187

Updated 16-Jul-18 22:04pm

CHill60

v2

Add a Solution

Comments

Animesh Datta 17-Jul-18 2:57am

have you tried something ?
put your effort

Member 13518187 17-Jul-18 3:02am

Not getting that's why I came here to ask

Patrice T 17-Jul-18 3:05am

And you some code ?

2 solutions

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)

CHill60 · Answer 1 · 2018-07-16T22:04:00

It's difficult to understand exactly what you are trying to do from

Quote:
Condition - If Column A(data type-Float) is Negative then Put -1 Sign in Column B and vice versa

The code you already have is putting a value in ColumnB and appears to work, but you have referred to a "sign".

So if ColumnB is a char type then you appear to want

SQL

update v_table1 set ColumnB= case when Isnull(ColumnA,0) <0 then '-' when Isnull(ColumnA,0) >0 then '1' else '0' end

Note I took out your null.

But, if what you really want is to negate ColumnB based on the value of ColumnA then you want something like this

SQL

update v_table1 set ColumnB= case when Isnull(ColumnA,0) <0 then -1 * ColumnB when Isnull(ColumnA,0) >0 then ColumnB else 0 end

Note this will only work if ColumnB is not already negative.

If this is not quite what you were asking then use the "Have a Question or Comment?" link next to this solution and I will try to help further

Member 13518187 · Answer 2 · 2018-07-16T21:39:00

Solution 1

update v_table1 set ColumnB= case when ColumnA <0 then -1 when ColumnA >0 then 1 when ColumnA =0 then 0 else null end

Posted 16-Jul-18 21:39pm

Member 13518187

Comments

CHill60 17-Jul-18 3:53am

By posting your code as a solution you have removed your question from the list of unanswered posts. There is an "Improve Question" link next to your question that you can use to update it with information like this. I will do it for you this time, but I suggest you delete this solution