How do flag the unselected records in a top selection

Question

I have a requirement I need to choose only a selected number of records from a group. However, I also need to flag the not choosen records in the even that they will need to be referred to at a later date.

I have over 80K records in Segment 1. The requirement is to select 50000 records

I've tried this:

UPDATE mytable
   SET [SuppressionReason] = 'REC LIMIT REACHED - S1'
 WHERE
   [ID] NOT IN 
    (
     SELECT TOP 50000 [ID] FROM mytable
      WHERE segment = '1' 
    );

However, this results in 0 records getting labeled in the SuppressionReason field as 'REC LIMIT REACHED - S1'. What am I missing or doing wrong?

You have `where segment = '1'` in the subquery but not in the outer query. That seems odd. — Gordon Linoff, Sep 27 '18 at 13:07
Have you checked that `SELECT TOP 50000 [ID] FROM mytable WHERE segment = '1'` returns the results you're expecting? — Diado, Sep 27 '18 at 13:08
can ID be NULL? is it the problem similar to 'select 'true' where 3 not in (1, 2, null)' — Cato, Sep 27 '18 at 13:21
And, Gordon, you are correct. I edited the query to: UPDATE mytable SET [SuppressionReason] = 'REC LIMIT REACHED - S1' WHERE Segment = 1 and [ID] NOT IN ( SELECT TOP 50000 [ID] FROM mytable WHERE segment = '1' ); But the result is still 0 — Nylah, Sep 27 '18 at 13:23
I recon that SELECT TOP 50000 [ID] FROM mytable WHERE segment = '1' is returning a null somewhere, meaning you select no records - see https://stackoverflow.com/questions/129077/not-in-clause-and-null-values — Cato, Sep 27 '18 at 13:25
oh, ok then! have you experimented with select statements to see if records are actually being selected for update? does SELECT TOP 50000 [ID] FROM mytable WHERE segment = '1' actually return only a subset of what is in MyTable? — Cato, Sep 27 '18 at 13:25
Is ID unique? Perhaps there are less then 50,000 unique IDs. — Derrick Moeller, Sep 27 '18 at 13:44
There is not enough information in the question to reproduce the issue. — Tab Alleman, Sep 27 '18 at 13:52

score 2 · Answer 1 · answered Sep 27 '18 at 13:30

Based on testing with the following code, are you absolutely certain that you have more than 50,000 records?

DROP TABLE IF EXISTS #TEMP
CREATE TABLE #TEMP
(
    ID INT IDENTITY(1,1),
    FIRSTNAME VARCHAR(10),
    LASTNAME VARCHAR(10),
    SEGMENT INT,
    SUPPRESSION VARCHAR(10)
)

INSERT INTO #TEMP 
    (FIRSTNAME, LASTNAME, SEGMENT)
VALUES 
    ('JOHN', 'KRAMER',1),
    ('MATT','GEORGE',1),
    ('PHILIP','MCCAIN',1),
    ('ANDREW','THOMAS',1)

UPDATE #TEMP
    SET SUPPRESSION = 'YEP'
WHERE ID NOT IN 
    (SELECT TOP(2) ID FROM #TEMP WHERE SEGMENT = 1)

SELECT * FROM #TEMP

This produces the following output, which I suspect is exactly what you are expecting to get.

   1    JOHN    KRAMER  1   NULL
   2    MATT    GEORGE  1   NULL
   3    PHILIP  MCCAIN  1   YEP
   4    ANDREW  THOMAS  1   YEP

How do flag the unselected records in a top selection

1 Answers1