Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.