update values of sql table using the values from the same table

Question

i have a table in sql server.

id    start    end     value    flag
 1      10     20.2    102       T
 2      22     11      133       T
 3      12.1   15      pending    F
 4      10     20.2    pending    F
 5      22     11      pending    F

as you can see, start and end is same for row 1 and 4, and row 2 and 5. But value ofrow with id 1 is 102, and that of 4 is pending,

i want to write a query, which looks for another occurance of same start and end and then update the column value to the value of respective start and end, if it is pending. else leave it

i.e I want to update the value column of the rows with id 4 and 5, with values in row with id 1 and 2.(pending to the respective value)

final result i am looking for is this:

id    start    end     value    flag
 1      10     20.2    102       T
 2      22     11      133       T
 3      12.1   15      pending    F
 4      10     20.2    102       F
 5      22     11      133       F

I don't even understand the change you made to the question. — Gordon Linoff
– Gordon Linoff, Commented Jun 20, 2017 at 10:54
@GordonLinoff for row with id 1, there is a particular value(102) for start=10 and end=20.2 , as you can see, in row with id 4, the start and end are same as that of row 1, but the value is pending in row 4. i want to update the pending to the value of row with id 1, since in both rows, start and end are same — Shubham R
– Shubham R, Commented Jun 20, 2017 at 10:58

DhruvJoshi · Accepted Answer · 2017-06-20 11:20:59Z

1

You can try a query like below

update t1
set t1.value=t2.value
from tbl t1 
join
tbl t2 on 
t1.start=t2.start and t1.end=t2.end and t2.value not like 'pending'
and t1.value like 'pending'

For you comment

if i havd to update Flag column also then?

please try this version

update t1
set t1.value=t2.value,
t1.flag=t2.flag
from tbl t1 
join
tbl t2 on 
t1.start=t2.start and t1.end=t2.end and t2.value not like 'pending'
and t1.value like 'pending'

edited Jun 20, 2017 at 11:20

answered Jun 20, 2017 at 10:58

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2017-06-20 10:58:20Z

1

This answers the first version of the question, which I understood.

update t
    set status = 'done'
    where status = 'pending' and
          exists (select 1
                  from t t2
                  where t2.start = t.start and t2.end = t.end and
                        t2.id <> t.id
                 );

Actually, I assume you want at least one row to be 'done' (your question is not clear. If so, then add t2.status = 'done' to the subquery.

Or, in SQL Server, you can use an updatable CTE:

with toupdate as (
      select t.*, count(*) over (partition by start, end) as cnt
      from t
     )
update toupdate
    set status = 'done'
    where status <> 'done' and cnt > 1;

In this case, you would use sum(case when status = 'done' then 1 else 0 end) over (partition by start, end) as cnt instead of count(*).

edited Jun 20, 2017 at 10:58

answered Jun 20, 2017 at 10:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

5 Comments

Shubham R Over a year ago

sir, i edited my question, actually, status is changed to value, ie. it may contain any integer. Please check

Gordon Linoff Over a year ago

@SRingne . . . It is rude to change a question after it has been answer. You invalidate answers and that can draw downvotes.

Shubham R Over a year ago

I was just editing the question sir, i'm very sorry, but while editing your answer came. This seem very complex query for me, i'm a beginner

mohan111 Over a year ago

@SRingne modify the Gordon query and put Value column in the place of 'Done'

Shubham R Over a year ago

@mohan111 ok sir, i will try, did you mean set value= select value.... ?

mohan111 · Accepted Answer · 2017-06-20 11:10:22Z

0

Just I'm modifying Gordon Query as that's is the best answer you can get for the question

Query 1 :

 update t
        set status =

t.value >> Remove 'Done'

 where status = 'pending' and
          exists (select 1
                  from t t2
                  where t2.start = t.start and t2.end = t.end and
                        t2.id <> t.id
                 );

Query 2 :

 with toupdate as (
          select t.*, count(*) over (partition by start, end) as cnt
          from t
         )
    update T
        set status =

T.Value >> Remove 'Done'

 From toupdate
        where status <> 'done' and cnt > 1;

answered Jun 20, 2017 at 11:10

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Collectives™ on Stack Overflow

update values of sql table using the values from the same table

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related