Improving Oracle SQL Query

Question

I'm trying to improve the below code. The unique identifier in this code is result_label_seq, but I still need to make sure that it's uniquely identified with facility_id and service_code. The display_seq shows in what order that this is printed on screen, basically in which row. Row 1, 2, 3, 4....this can go all the way up to 20.

What I'm wanting to do is assign the display_seq + 1 for each entry of the result_label_seq that I have. This is currently how I'm having to write it.

 SELECT * FROM anc_result_seqs WHERE service_code = 'CBC' AND result_label_seq ='87484' AND facility_id = C' 

 UPDATE anc_result_seqs SET display_seq = '1' WHERE service_code = 'CBC' AND result_label_seq = '87484' AND facility_id = 'C'
 UPDATE anc_result_seqs SET display_seq = '2' WHERE service_code = 'CBC' AND result_label_seq = '87485' AND facility_id = 'C'
 UPDATE anc_result_seqs SET display_seq = '3' WHERE service_code = 'CBC' AND result_label_seq = '87560' AND facility_id = 'C'
 UPDATE anc_result_seqs SET display_seq = '4' WHERE service_code = 'CBC' AND result_label_seq = '87579' AND facility_id = 'C'

 .....(continued)

Is there a way to do this in Oracle SQL?

Answer 1

Firstly: you should value literals that match the data type of the columns. '1' is a character, where 1 is a number. Avoid any implicit data type conversion.

I assume that display_seq and result_label_seq are really defined as number not varchar in the following statements. If they are indeed varchar columns you should change that. Never store numbers in character columns.

If you just want to increment the values, you can use

UPDATE anc_result_seqs
   SET display_seq = display_seq + 1
WHERE service_code = 'CBC' 
  AND facility_id = 'C'

However if you want to renumber the display_seq according to the current ordering that is defined by the column result_label_seq then the following should do:

MERGE INTO anc_result_seqs ars
USING (
   SELECT rowid as rid, 
          row_number() over (order by result_label_seq) as rn
   FROM result_label_seq
   WHERE service_code = 'CBC' 
   AND facility_id = 'C'
) t ON (t.rid = ars.rowid)
WHEN MATCHED THEN UPDATE 
   SET display_seq = rn;