Updating table by using multiple joins on the same table

Question

In the picture that is attached, I highlighted two columns. The left column is a the StartTime and the right column is an ID. I need the IDs that are NULL to be 164, since all 4 of those rows have the same exact StartTime. Is there a way to code one update statement to gather the NULL rows and rows with a value, and then update the NULL rows with a value that has the same StartTime? Any code would be very helpful.

Picture: Query Results

Looks like this question could help http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — Simon, Apr 21 '16 at 17:05

score 0 · Accepted Answer · edited Apr 22 '16 at 16:35

0

UPDATE t
SET    t.ID = x.ID
FROM   YourTable t
INNER JOIN (SELECT DISTINCT ID, StartTime
            FROM YourTable
            WHERE ID IS NOT NULL
            ) x 
ON     x.StartTime = t.StartTime
WHERE  t.ID IS NULL

This is how I would do it.

edited Apr 22 '16 at 16:35

SQL and Java Learner

671
2
6
18

answered Apr 21 '16 at 17:16

dporter

16
3

Thank you very much!!! I was trying to get a list of NULLs and not NULLs, but that was a lot simpler. – SQL and Java Learner Apr 21 '16 at 17:45

Updating table by using multiple joins on the same table

1 Answers1