Select Query Weirdness

  • five_ten_fiftyfold

    Ten Centuries

    Points: 1146

    Hi,

    I've been given a spreadsheet containing a list of identity values that our marketing team need updating. I absentmindedly copied the column and pasted it into an online delimiter so I could paste this into management studio to check how many of the rows I'd be updating.

    The reason I say I absentmindedly pasted the info is that I forget to remove the title from the column, when I came to run the query it gave me an, erm, unexpected result set.

    This is a (heavily) truncated version of the script I ran.

    SELECT * FROM MyTable AS MT
    WHERE MT.CustID IN (CustID,112946,124446,670699,179255,726549,186510,186438);

    So, with the CustID in the 'IN' selection the query returned every row in the table. The column is an Integer so I would have expected the query to fail. I can only something is getting messed up by an implicit conversion.

    Can anyone explain what's happening here?

  • five_ten_fiftyfold

    Ten Centuries

    Points: 1146

    I think I'm being silly here, I'm combining the column name with explicitly specified values, I didn't think that this was valid syntax. Surprisingly, to me anyway, it seems like it is.

  • @Taps

    SSC Eights!

    Points: 868

    Don't have an answer to this - but learned something new today. I would have expected it to fail as well but tested it out and it works !

  • PB_BI

    SSCoach

    Points: 17095

    It's because CustID is a column name. Essentially your where clause is doing 1=1.

    The following will fail with an "Invalid column name" error:

    DROP TABLE IF EXISTS #noddy

    CREATE TABLE #noddy
    (NoddyID INT IDENTITY(1,1),
    NoddyString VARCHAR(20),
    NoddyUniString NVARCHAR(20),
    NoddyInt INT)

    INSERT INTO #noddy (NoddyString, NoddyUniString, NoddyInt)

    VALUES ('Five', 'Ten Fiftyfold', 1900),
    ('From', 'The Flagstones', 1901),
    ('Hither', 'to', 1902),
    ('Musette', 'and Drums', 1903)

    SELECT
    *
    FROM
    #noddy
    WHERE
    NoddyInt IN (1900, 1903, Hither)

    But this will succeed and return all rows:

    SELECT
    *
    FROM
    #noddy
    WHERE
    NoddyInt IN (1900, 1903, NoddyInt)

     


    I'm on LinkedIn

  • Phil Parkin

    SSC Guru

    Points: 244598

    You must work in Toyland.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • five_ten_fiftyfold

    Ten Centuries

    Points: 1146

    Can speak for anyone else, but this place feels like a circus sometimes.

  • PB_BI

    SSCoach

    Points: 17095

    Phil Parkin wrote:

    You must work in Toyland.

    Where others use variations of "foobar" for things they can't think of a name for, I use variations of "noddy". You're not the first who's commented on it. I have no idea where it came from and I'm aware that it makes no sense linguistically but I find, as with most things, I just don't care =D


    I'm on LinkedIn

  • Phil Parkin

    SSC Guru

    Points: 244598

    PB_BI wrote:

    Phil Parkin wrote:

    You must work in Toyland.

    Where others use variations of "foobar" for things they can't think of a name for, I use variations of "noddy". You're not the first who's commented on it. I have no idea where it came from and I'm aware that it makes no sense linguistically but I find, as with most things, I just don't care =D

    I'm old enough to know exactly where it came from & wholeheartedly approve. My temp tables are usually called #crap. Must do better.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeff Moden

    SSC Guru

    Points: 996676

    Phil Parkin wrote:

    PB_BI wrote:

    Phil Parkin wrote:

    You must work in Toyland.

    Where others use variations of "foobar" for things they can't think of a name for, I use variations of "noddy". You're not the first who's commented on it. I have no idea where it came from and I'm aware that it makes no sense linguistically but I find, as with most things, I just don't care =D

    I'm old enough to know exactly where it came from & wholeheartedly approve. My temp tables are usually called #crap. Must do better.

     

    My favorite temp table is #MyHead.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply