copyng values from one column to another sql

Question

I have a list like this:

| slope | lower_limit | upper_limit | limit |
|-------|-------------|-------------|-------|
|  -0.5 |          23 |          25 |     0 |
|   0.6 |          23 |          25 |     0 |
|   0.7 |          23 |          25 |     0 |

I want to update values of the limit column from the lower limit and upper limit columns, depending of the sign of the slope column. I would like the resulting table to look like this:

| slope | lower_limit | upper_limit | limit |
|-------|-------------|-------------|-------|
|  -0.5 |          23 |          25 |    23 |
|   0.6 |          23 |          25 |    25 |
|   0.7 |          23 |          25 |    25 |

I am currently using an if, but it changes the value of the whole column, resulting in all values in the limit column being 25

What do you mean depending of the sign of the slope ? depending on what logic? — D-Shih
– D-Shih, Commented Oct 4, 2018 at 21:42
if the slope is negative i need the lower_limit and if the slope is positive i need the upper_limit — Edu Galindo
– Edu Galindo, Commented Oct 4, 2018 at 21:43

D-Shih · Accepted Answer · 2018-10-04 21:43:54Z

4

I think you can try to use CASE WHEN in update.

CREATE TABLE T(
   slope FLOAT, 
  lower_limit INT, 
  upper_limit  INT,
  limit INT
);

INSERT INTO T  VALUES (-0.5,23,25,0);
INSERT INTO T  VALUES (0.6,23,25,0); 
INSERT INTO T  VALUES (0.7,23,25,0);


UPDATE T
SET limit = CASE WHEN slope > 0 THEN upper_limit 
                    ELSE lower_limit  END

Query 1:

select * from t

Results:

| slope | lower_limit | upper_limit | limit |
|-------|-------------|-------------|-------|
|  -0.5 |          23 |          25 |    23 |
|   0.6 |          23 |          25 |    25 |
|   0.7 |          23 |          25 |    25 |

answered Oct 4, 2018 at 21:43

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

copyng values from one column to another sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related