CODEWITHSUNDEEP
t-sqlsql-server-2005
dcpartners
dcpartners

Reputation: 5456

How to update from other table based on sequence?

I've managed to get this: How to get the end of entry of the first column in TSQL?

The updated data as follow and managed to get the last record as well updated.

Service; Sequence; EndSequence  
A, 2089697; 2089737
B, 2089738; 2089837
C, 2089838; 2090367
D, 2090368; 2090399
E, 2090400; 2090500

Now I want to update the master table which has million records on it based on the sequence start and end start on the ID. I can do this via cursor but I prefer via normal query.

BTW I'm using SQL 2005.

The Master table as follow:

ID; Value; Service
2089697; 23; null
...
2090500; 12; null

so the result will be:

ID; Value; Service
2089697; 23; A
... 
2089737; 45; A
2089738; 45; B
...
2089837; 34; B 
2089838; 23; C
...

Thanks

Upvotes: 0

Views: 63

Answers (1)

Andriy M
Andriy M

Reputation: 77707

More than one option here.

  1. Correlated subquery:

    UPDATE MasterTable
SET Service = (
  SELECT s.Service
  FROM SequenceTable s
  WHERE MasterTable.ID BETWEEN s.Sequence AND s.EndSequence
)
WHERE Service IS NULL
;

  2. Inner join:

    UPDATE MasterTable
SET Service = s.Service
FROM SequenceTable s
WHERE MasterTable.Service IS NULL
  AND MasterTable.ID BETWEEN s.Sequence AND s.EndSequence
;

    Alternative syntax:

    UPDATE m
SET Service = s.Service
FROM MasterTable m
INNER JOIN SequenceTable s
  ON m.ID BETWEEN s.Sequence AND s.EndSequence
WHERE m.Service IS NULL
;

Both options assume that the ranges in SequenceTable do not overlap.

Upvotes: 1

Related Questions