sql server 2005 ----- how to update multiple rows using subquery

Question

I have 2 tables named as member and tree_str...

"member" table as follows

self_id parent_id   position    
100     ROOT        ROOT    
101     100         LEFT    
102     100         CENTRE  
103     100         RIGHT   
104     101         LEFT    
105     101         CENTRE  
106     101         RIGHT

"tree_str" table as below....

self_id  L1_Left     L1_center   L1_Right  
100     
101     
102     
103     
104     
105     
106

now i wanna populate second table using first one like this manner.....

self_id  L1_Left     L1_center   L1_Right  
100    101             102         103  
101    104             105         106  
102     
103     
104     
105     
106

................ i use the query........

update tree_str 
    set tree_str.L1_Left = (select member.self_id from tree_str 
    INNER JOIN member 
    on member.parent_id=tree_str.self_id 
    AND member.position='LEFT')
    WHERE self_id IN (select member.parent_id from tree_str INNER JOIN member 
    on member.parent_id=tree_str.self_id and member.position='LEFT')

but it gives the result.......

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery 
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

so plz suggest me the right query to do it...........?????

score 1 · Answer 1 · answered Dec 21 '12 at 08:23

1

Why not rather try something like

UPDATE  tree_str
SET     L1_Left = ml.self_id,
        L1_center = mc.self_id,
        L1_right = mr.self_id
FROM    tree_str t LEFT JOIN
        member ml   ON  t.self_id = ml.parent_id
                    AND ml.position = 'LEFT' LEFT JOIN
        member mc   ON  t.self_id = mc.parent_id
                    AND mc.position = 'CENTRE' LEFT JOIN
        member mr   ON  t.self_id = mr.parent_id
                    AND mr.position = 'RIGHT'

answered Dec 21 '12 at 08:23

Adriaan Stander

162,879
31
289
284

now plz tell me, how to use this query with in a trigger which is automatically fire when self_id column is filled with next record???? – manish Dec 21 '12 at 15:22

NigelK · Answer 2 · 2012-12-21T08:46:42.663

The reason that your subquery returns more than one value, is that it is not correlated with the row being updated. Try the SQL below.

update ts
 set ts.L1_Left =
  (select m.self_id
   FROM member m
     WHERE m.parent_id = ts.self_id
     AND m.position='LEFT')
FROM tree_str ts
WHERE self_id IN
  (select member.parent_id
   from tree_str
   INNER JOIN member
      on member.parent_id=tree_str.self_id
     and member.position='LEFT')

Note that correlated subqueries can be expensive so Astander's answer looks like it may be more efficient. However hopefully this helps to explain where you were going wrong with your original syntax.

sql server 2005 ----- how to update multiple rows using subquery

2 Answers2