Search code examples
sqlsql-servert-sqlsql-server-2014

TSQL: How do I do an UPDATE based on a string and a date?

I was given a spreadsheet of students where 500 students don't have student ID's. I imported this into SQL as a table, I'll call it 'table a'.

I have another table with all students and their ID's, complete with birthdate etc. I'll call it 'table b'.

Goal:
Copy the student ID's from table b into table a. To do this, I think I need to update table a based on the student name and birthdate.

Problem:
My update query is duplicating student id's to students who have the same birthdate, but they have different last names. So the result is two different students with the same birthdate end up having the same student id.

How can I update table a with the correct student ID's please?

My current update statement that's putting duplicate ID's on the same student:

UPDATE table a
SET EMPStudentID = CAStudentID
FROM #students
WHERE EmpStudentName = CA_STUNAME
         AND EMP_DOB = CA_DOB

Thank you.

Screenshot of sample data: enter image description here

Code of sample data:

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
Solution

  • Try this below

    UPDATE  S
SET EMPStudentID = A.CAStudentID

FROM #students S 
INNER JOIN #TableA A
ON S.EmpStudentName = A.CA_STUNAME
         AND S.EMP_DOB = A.CA_DOB

SELECT *  FROM #students