update the table using same table

Question

I want to update rows of scenario_id=1 from scenario_id=7 where name matches. The difference between scenario_id is not constant.

id|scenario_id|status|name
1 |1          |Passed|testcase_1
2 |1          |Passed|testcase_2
3 |1          |Failed|willPassInNextRun
4 |7          |Passed|testcase_1
5 |7          |Passed|willPassInNextRun

after the update table should look like below

id|scenario_id|status|name
1 |1          |Passed|testcase_1
2 |1          |Passed|testcase_2
3 |1          |Passed|willPassInNextRun
4 |7          |Passed|testcase_1
5 |7          |Passed|willPassInNextRun

What is the result you want ? Because I am not sure to understand. — kmas
– kmas, Commented Jul 4, 2013 at 15:39
Can you write a SELECT query that returns the correct result? — Strawberry
– Strawberry, Commented Jul 4, 2013 at 15:40
What should be updated? How should the table look afterwards? — CL.
– CL., Commented Jul 4, 2013 at 17:02

CL. · Accepted Answer · 2013-07-05 07:44:53Z

1

To make it work also in SQLite, use standard SQL and look up the new value with a correlated subquery:

UPDATE MyTable
SET status = COALESCE((SELECT status
                       FROM MyTable AS T2
                       WHERE scenario_id = 7
                         AND T2.name = MyTable.name),
                      status)
WHERE scenario_id = 1

answered Jul 5, 2013 at 7:44

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Comments

Nitesh Mishra · Accepted Answer · 2013-07-04 15:58:17Z

0

Try this,

update table t1, table t2
 set t1.status=t2.status
 where t2.scenario_id=7 and t1.scenario_id=1
 and t1.name=t2.name
 and t1.status!=t2.status;

answered Jul 4, 2013 at 15:58

Nitesh Mishra

3111 silver badge10 bronze badges

Collectives™ on Stack Overflow

update the table using same table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related