December 17, 2009 at 11:21 am
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?
December 17, 2009 at 11:32 am
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
December 17, 2009 at 11:35 am
Yes, Jason's right, I tried it, was getting the same error.
Better for you, if you test the script before posting..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 17, 2009 at 11:40 am
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')
December 17, 2009 at 12:08 pm
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 )
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 17, 2009 at 12:22 pm
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
December 17, 2009 at 12:24 pm
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
December 17, 2009 at 12:27 pm
Thank you very much!
December 17, 2009 at 12:32 pm
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