update query error

• newbie_sql

  SSC Eights!

  Points: 954

  More actions

  December 9, 2009 at 3:28 am

  #228409

  I am writing the following update query which is giving me an error saying more than 1 records returned by the subquery

  update

  p_audit set j_num = (select c.new_j_num from tbl1 a,p_audit b,tbl2 c where a.p_numr=b.new_p_num and a.tbl1_id=c.tbl2_id)

  However if I run the 'select c.new_j_num from tbl1 a,p_audit b,tbl2 c where a.p_numr=b.new_p_num and a.tbl1_id=c.tbl2_id' query standlaone it runs fine & Returns 1 row per id

  Is there an alternate way to write this update query??

• arun.sas

  SSChampion

  Points: 11831

  More actions

  December 9, 2009 at 3:42 am

  #1089722

  Hi,

  Unless the table schema and few data, it’s hard to take the decision, however try this

  update a

  set a.j_num = c.new_j_num

  from p_audit a,tbl1 b, tbl2 c

  where

  b.p_numr=a.new_p_num and

  b.tbl1_id=c.tbl2_id

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 9, 2009 at 5:24 am

  #1089752

  charu.verma (12/9/2009)

  ---

  I am writing the following update query which is giving me an error saying more than 1 records returned by the subquery

  update

  p_audit set j_num = (select c.new_j_num from tbl1 a,p_audit b,tbl2 c where a.p_numr=b.new_p_num and a.tbl1_id=c.tbl2_id)

  However if I run the 'select c.new_j_num from tbl1 a,p_audit b,tbl2 c where a.p_numr=b.new_p_num and a.tbl1_id=c.tbl2_id' query standlaone it runs fine & Returns 1 row per id

  Is there an alternate way to write this update query??

  -- uncorrelated subquery

  update p_audit set j_num = (

  select c.new_j_num

  from tbl1 a, p_audit b, tbl2 c

  where a.p_numr = b.new_p_num

  and a.tbl1_id = c.tbl2_id)

  -- correlated subquery

  update p_audit set j_num = (

  select c.new_j_num

  from tbl1 a, tbl2 c

  where a.p_numr = p_audit.new_p_num

  and a.tbl1_id = c.tbl2_id)

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden