Search code examples
sqlrdbms

Update primary key starting character

I have a table student and it has a primary key student_id, student_id is referred across multiple tables. student_id format is P123X12

Tables are as follows

  • student( student_id is PK)
  • audit_trail (student_id is FK)
  • Result( student_id is FK)
  • more 10 tables which are referring to student_id

I have 100 records in Result table, I want to update student_id of those records from P123X12 to D123X12. is there anyway I can change these referred values?

Solution

  • As per my comment, you could duplicate the data in student. Alter your FK fields, then delete the original records from student. You will probably want to tweak the where clause if you're not changing ALL fields that start with a P.

    /* Duplicate our data in student */
INSERT INTO student (student_id, field2, field3, field4)
SELECT
'D' + SUBSTRING(student_id,2,LEN(student_id)-1)
,field2
,field3
,field4
FROM student
WHERE student_id LIKE 'P%'

/* Update our other tables */
UPDATE audit_trail
SET student_id = 'D' + SUBSTRING(student_id,2,LEN(student_id)-1)
WHERE student_id LIKE 'P%'

UPDATE Result
SET student_id = 'D' + SUBSTRING(student_id,2,LEN(student_id)-1)
WHERE student_id LIKE 'P%'

/* Delete the original rows from student */
DELETE student
WHERE student_id LIKE 'P%'

    A slightly longer way would be to extract your original fields into a temp table and insert from there. You could then delete from student with an inner join to the temp table to make sure you're only deleting the rows that you definitely want to.