Help in writing sql

  • CREATE table From_Upd(

    Col_01 Char(1) Not Null,

    Col_02 Varchar(20) null )

    INSERT INTO From_Upd (Col_01,Col_02) Values ('1','''01'',''02'',''03'',''04''')

    INSERT INTO From_Upd (Col_01,Col_02) Values ('2','''A'',''B'',''C'',''D''')

    INSERT INTO From_Upd (Col_01,Col_02, Statement) Values ('3','','Statement for 3')

    INSERT INTO From_Upd (Col_01, Col_02,Statement) Values ('4','','Statement for 4')

    CREATE table To_Upd(ColA Char(1) Not Null)

    INSERT INTO To_Upd (ColA) Values ('A')

    INSERT INTO To_Upd (ColA) Values ('B')

    INSERT INTO To_Upd (ColA) Values ('C')

    INSERT INTO To_Upd (ColA) Values ('D')

    --This statement works

    SELECT * FROM To_upd

    WHERE ColA IN ('A','B','C')

    --I need to make this work which should result same as above

    SELECT * FROM To_upd

    WHERE ColA IN(Select Col_02 FROM From_upd WHERE Col_01 = '2')

    Please suggest how to write it?

  • The second set of insert statements for your FROM_UPD table do not match your table definition.

    Could you clarify which set of statements and the requirements?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Yes, Jason's right, I tried it, was getting the same error.

    Better for you, if you test the script before posting..


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • I'm sorry I meant to delete those before posting.

    So here it is-

    CREATE table From_Upd(

    Col_01 Char(1) Not Null,

    Col_02 Varchar(20) null )

    INSERT INTO From_Upd (Col_01,Col_02) Values ('1','''01'',''02'',''03'',''04''')

    INSERT INTO From_Upd (Col_01,Col_02) Values ('2','''A'',''B'',''C'',''D''')

    CREATE table To_Upd(ColA Char(1) Not Null)

    INSERT INTO To_Upd (ColA) Values ('A')

    INSERT INTO To_Upd (ColA) Values ('B')

    INSERT INTO To_Upd (ColA) Values ('C')

    INSERT INTO To_Upd (ColA) Values ('D')

    --This statement works

    SELECT * FROM To_upd

    WHERE ColA IN ('A','B','C')

    --I need to make this work which should result same as above

    SELECT * FROM To_upd

    WHERE ColA IN (Select Col_02 FROM From_upd WHERE Col_01 = '2')

  • Add this to the existing one, remove the non working query.

    DECLARE @SQL123 VARCHAR(8000)

    SELECT @SQL123 = Col_02 FROM From_upd WHERE Col_01 = '2'

    SET @SQL123 = '(' + @SQL123 + ')'

    EXEC ('SELECT * FROM To_upd WHERE ColA IN ' + @SQL123 )


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Or this would work too:

    ;WITH uppity (id, lft, rght, idx)

    AS

    (

    SELECT t.col_01

    ,LEFT(t.col_02, CHARINDEX(''',''', t.col_02) - 1)

    ,SUBSTRING(t.col_02, CHARINDEX(''',''', t.col_02) + 2, DATALENGTH(t.col_02))

    ,0

    FROM from_upd t

    where col_01=2

    UNION ALL

    SELECT c.id

    ,CASE WHEN CHARINDEX(''',''', c.rght) = 0 THEN c.rght ELSE LEFT(c.rght, CHARINDEX(''',''', c.rght) - 1) END

    ,CASE WHEN CHARINDEX(''',''', c.rght) > 0 THEN SUBSTRING(c.rght, CHARINDEX(''',''', c.rght) + 2, DATALENGTH(c.rght))

    ELSE '' END

    ,idx + 1

    FROM uppity c

    WHERE DATALENGTH(c.rght) > 0

    )

    select * from to_upd

    Where cola in (select replace(lft,'''','') from uppity)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Three words: Beware SQL Injection.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you very much!

  • This seems to work:

    SELECT F.ColA

    FROM To_upd T join From_upd F on charindex(T.ColA, F.Col_02) > 0

    WHERE F.Col_01 = '2'

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

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