CODEWITHSUNDEEP
sqlsql-server
Shubham R
Shubham R

Reputation: 7644

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

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

Upvotes: 1

Views: 73

Answers (3)

DhruvJoshi
DhruvJoshi

Reputation: 17126

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'

Upvotes: 1

mohan111
mohan111

Reputation: 8865

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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(*).

Upvotes: 1

Related Questions