October 24, 2012 at 7:26 am
Hi I have something like this and the tables have no foreign keys. I am using date in where clause and i am not able to use id because it is deleting al the data.
DELETE t1 FROM table1 t1
INNER JOIN table2 t2 ON
t1.id= t2.id
and t1.code= t2.code
inner join table3 t3 on
t2.id=t3.id
t2.code=t3.code
WHERE t3.date IN
(
SELECT [DATE] FROM t3
WHERE CNTYCODE IN('A', 'B', 'BA', 'G', 'N')
AND [DATE] < '01/14/2010']
UNION ALL
-- Region-I
SELECT [DATE] FROM t3
WHERE CNTYCODE IN('c', 'd', 'e', 'f', 'k')
AND [DATE] < '01/14/2010']
)
can i use this way or no.
October 24, 2012 at 7:42 am
Please post the sample data and table scripts
Malleswarareddy
I.T.Analyst
MCITP(70-451)
October 24, 2012 at 7:54 am
raghuveer126 (10/24/2012)
Hi I have something like this and the tables have no foreign keys. I am using date in where clause and i am not able to use id because it is deleting al the data.DELETE t1 FROM table1 t1
INNER JOIN table2 t2 ON
t1.id= t2.id
and t1.code= t2.code
inner join table3 t3 on
t2.id=t3.id
t2.code=t3.code
WHERE t3.date IN
(
SELECT [DATE] FROM t3
WHERE CNTYCODE IN('A', 'B', 'BA', 'G', 'N')
AND [DATE] < '01/14/2010']
UNION ALL
-- Region-I
SELECT [DATE] FROM t3
WHERE CNTYCODE IN('c', 'd', 'e', 'f', 'k')
AND [DATE] < '01/14/2010']
)
can i use this way or no.
Syntactically I don't see anything wrong but I highly doubt the logic is correct. You where clause is way more complicated than it needs to be. This should be the same thing.
WHERE t3.date > '01/14/2010'
and t3.CNTYCODE IN('A', 'B', 'BA', 'G', 'N', 'c', 'd', 'e', 'f', 'k')
The real problem is that you will delete anything from table1 that has a date greater than 1/14/2010 which I doubt is what you really want.
If you want some real help you need to post ddl, sample data and an explanation of what you want to happen. Take a look at the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply