SQL update values by comparing it to other entries

Niklas 0
Viewed 29

so I got Questionnaire Data where the same Questions got asked multiple times, sadly they didnt think of giving them different question_ids, but at least i have the timestamps of when they were answered. Similar to this

person_id question_id answer timestamp
12 q1l1 0.4 12:38
12 q1l1 0.7 12:54

Is there a easy way to change the question id based on timestamps?

So to grab dublicates of the same question_id for the same person_id and compare the timestamps and set the question_ids to q1l1_1 and q1l1_2

person_id question_id answer timestamp
12 q1l1_1 0.4 12:38
12 q1l1_2 0.7 12:54
sql postgresql
2 Answers

Window function is your approach:

select 
    person_id, 
    question_id || '_' || row_number() over (partition by person_id, question_id order by timestamp),
    answer numeric, 
    timestamp
from questionnaire;

sql online editor

Slava Rozhnev0

You can use ROW_NUMBER() function as the following:

If you want only select the new question_id:

select person_id,
       question_id || '_' || ROW_NUMBER() OVER 
       (PARTITION BY person_id ORDER BY timestamp) question_id,
       answer,
      timestamp
from table_name

If you want to update question_id to the new value:

update table_name set question_id = D.new_question_id
from  
(
  select person_id,question_id,
         question_id || '_' || ROW_NUMBER() 
         OVER (PARTITION BY person_id ORDER BY timestamp) new_question_id,
         answer, timestamp
  from table_name
) D
where table_name.person_id=D.person_id and
      table_name.question_id=D.question_id and
      table_name.timestamp=D.timestamp

See a demo.

I think you don't need the condition table_name.question_id=D.question_id since all question_ids are the same for the same user.

userGpjR0
Related