CODEWITHSUNDEEP
sqloracle-databasesubquery
Paulo Victor
Paulo Victor

Reputation: 337

making an update query through a subquery using ROW_NUMBER()?

This query does not work ERROR: 01427. 00000 - "single-row subquery returns more than one row":

UPDATE table_name SET column_update =( 
  SELECT ROW_NUMBER() 
  OVER(order by column_day_job) from table_name
) WHERE column_pearson_id = 808;

enter image description here

enter image description here

enter image description here

This SELECT works perfectly. I want to copy the ROW_NUMBER () values for sequence_day_job column, in the same order

SELECT  column_person_id, column_day_job, sequence_day_job,
ROW_NUMBER()
  OVER (PARTITION BY column_person_id
      ORDER BY column_day_job)
  FROM table_name;

UPDATE 10/09/2015 (01) - UPDATE tried to accomplish something like this, but it did not work, if a temporary table would solve my problem? It's something that seems to be relatively simple for the use of a temporary table, and cmo will be a recurring believe that use of temporary table is not so interesting.

 UPDATE table_name SET sequence_day_job = (
 SELECT ROW_NUMBER() OVER (PARTITION BY column_person_id ORDER BY column_day_job)  
       FROM PC_COMENTARIO )xx

)WHERE column_person_id = 808; //ERROR: 01427. 00000 - "single-row subquery returns more than one row"

Upvotes: 1

Views: 1152

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17924

This should work:

MERGE INTO table_name t
USING ( SELECT rowid rid, row_number() OVER (order by sequence_job_day) rn FROM table_name ) u
ON ( t.rowid = u.rid AND t.column_pearson_id = 888 )
WHEN MATCHED THEN UPDATE SET t.column_update = u.rn;

Note -- column_update will be unique across all rows in the table. If you just want a unique ordering within each person, you need to PARTITION BY column_pearson_id. Also, then you could put the =888 condition in the USING clause and take it out of the MATCH clause.

Upvotes: 1

Mr Smith
Mr Smith

Reputation: 3486

Why do you need a subquery. Doesn't this work:

UPDATE table_name
SET column_update = column_day_job

UPDATE - I think you need something like this. The syntax might be off, but I think you need you need to create a virtual table that has the row numbers you need & then pick out the correct row. If the dates in that table are unique, then you can use those.

UPDATE table_name t1 SET column_update =( 
  SELECT t1.rownum
  from (select ROW_NUMBER() from table_name t2) t3
  where t3.seq_day_job = t1.seq_day_job
) WHERE column_pearson_id = 808;

Upvotes: 0

Related Questions