Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date in where clause Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 7:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:41 AM
Points: 25, Visits: 156
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.
Post #1376461
Posted Wednesday, October 24, 2012 7:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,939, Visits: 1,162
Please post the sample data and table scripts


Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1376477
Posted Wednesday, October 24, 2012 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1376493
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse