Update multiple rows based on values in other rows in same table

Question

I need to update multiple rows based on value in other rows with matching id.

Table structure:

ID   |    Sub-ID | value
----------------------------
1    |      1    |    a
1    |      2    |    b
1    |      3    |    c
2    |      1    |    x
2    |      2    |    y
2    |      3    |    z
3    |      1    |    k
3    |      2    |    l
3    |      3    |    m

I need to update value of SubID = 2 with value of SubId=3 for a specific IDs (where ID in other table )

The result should be (base on the above):

ID   |    Sub-ID | value
----------------------------
1    |      1    |    a
1    |      2    |    c
1    |      3    |    c
2    |      1    |    x
2    |      2    |    y
2    |      3    |    z
3    |      1    |    k
3    |      2    |    m
3    |      3    |    m

What will be the most efficient way to implement it?

This what I have right now:

UPDATE data_tab tab1
   SET (value) =
          (SELECT tab2.value
             FROM data_tab tab2
            WHERE tab1.id = tab2.id 
              AND tab1.sub_id = 2 AND tab2.sub_id = 3 
              )      
 WHERE EXISTS (SELECT 1    FROM ids_table 
                WHERE id = tab1.id)

Gordon Linoff · Accepted Answer · 2013-09-15 12:21:30Z

2

The answer to your question is something like this:

UPDATE data_tab tab1
   SET value = (SELECT tab2.value
                FROM data_tab tab2
                WHERE tab1.id = tab2.id AND
                      tab2.sub_id = 3
              )
   WHERE tab1.id in (select id from ids_table) and
         tab1.sub_id = 2;

In other words, your original query is fine. I think it is more efficient to move the condition on sub_id = 2 to the outer query.

edited Sep 15, 2013 at 12:21

answered Sep 15, 2013 at 12:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Ben Over a year ago

"I need to update value of SubID = 2 with value of SubId=3 for a specific IDs (where ID in other table )"

Ben · Accepted Answer · 2013-09-15 12:25:48Z

1

What you've done looks okay; whatever happens you're going to have to do those table scans. It may be quicker (and it looks cleaner) if you use a MERGE statement:

merge into data_tab o
using ( select id, value
          from data_tab a
          join ids_table b
            on a.id = b.id
         where a.subid = 3
               ) n
   on ( o.id = n.id )
 when matched then
      update
         set o.value = n.value
       where o.subid = 2

edited Sep 15, 2013 at 12:25

answered Sep 15, 2013 at 12:10

Ben

53.1k36 gold badges133 silver badges156 bronze badges

2 Comments

A.B.Cade Over a year ago

maybe I don't understand OP right, but why do you need the ROWID ? sqlfiddle.com/#!4/48575/11

Ben Over a year ago

The rowid was a stupid mistake @A.B.Cade; it's normally more efficient in a self-join but I wasn't thinking about the WHERE clauses...

Collectives™ on Stack Overflow

Update multiple rows based on values in other rows in same table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related