TSQL: How can I make an UPDATE based on a string and date?
I was given a student spreadsheet where 500 students do not have student IDs. I imported this into SQL as a table, I will call it "table a".
I have another table with all the students and their IDs, date of birth, etc. I'll call it "table b".
Purpose:
Copy student ID from table b to table a. To do this, I think I need to update table a based on student name and date of birth.
Problem:
My update request duplicates student ID to students who have the same dates of birth but have different names. So the result is two different students with the same number of births and the same student ID.
How can I update table a with the correct student id please?
My current update instruction, which has a duplicate ID for the same student:
UPDATE table a
SET EMPStudentID = CAStudentID
FROM #students
WHERE EmpStudentName = CA_STUNAME
AND EMP_DOB = CA_DOB
Thank.
Screenshot with sample data:
Sample data code:
CREATE TABLE #students (
EMPStudentID int
, EmpStudentName varchar(30)
, EMP_DOB DATE
, CA_DOB DATE
, CA_STUNAME VARCHAR(30)
, CAStudentID int
)
INSERT INTO #students (EmpStudentName ,EMP_DOB ,CA_DOB ,CA_STUNAME ,CAStudentID)
VALUES
('Brothers, John', '20000309', '20000309', 'Brothers, John', 1111111),
('Campbell, Thomas', '20000107', '20000107', 'Campbell, Thomas', 2222222),
('Echols, Terry', '20000309', '20000309', 'Echols, Terry', 3333333),
('Jones, Bruce', '20000518', '20000518', 'Jones, Bruce', 4444444),
('Maxwell, Lauren', '20000728', '20000728', 'Maxwell, Lauren', 5555555),
('Feldler, John', '19991026', '19991026', 'Feldler, John', 6666666),
('Jenkins, Michael', '19990322', '19990322', 'Jenkins, Michael', 7777777),
('Taylor, Greg', '20000428', '20000428', 'Taylor, Greg', 8888888),
('Williams, Gene', '20000105', '20000105', 'Williams, Gene', 9999999),
('Wynn, Charles', '20000111', '20000111', 'Wynn, Charles', 1233211)
SELECT * FROM #students
source to share