By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

sql deletion with delimiters

P: 3
Have a problem with deletion that contains delimiters:

tb2 & tb3 contains Dept like: GM/ACCT/FIN
Want to delete it such that Dept is not found in tb1 & tb2

This query will still delete even if is found in tb1 & tb2:

Expand|Select|Wrap|Line Numbers
  1. delete a from tb1 a left join tb2 b on b.Dept=a.Dept
  2. left join tb3 c on c.Dept=a.Dept
  3. where a.Dept=@dept and b.Dept is null and c.Dept is Null
  4.  
Jul 1 '09 #1
Share this Question
Share on Google+
3 Replies


P: 49
Set null to Null as im not sure if it is case sensitive. Your variable should be in quotes also. Try this...

Expand|Select|Wrap|Line Numbers
  1. delete a from tb1 a left join tb2 b using(Dept)
  2. left join tb3 c using(Dept)
  3. where(a.Dept='@dept' and b.Dept is Null and c.Dept is Null )
Jul 1 '09 #2

P: 3
hi daniel2335,

this could not work as using is an incorrect syntax.

Still have problem with this query:
tb2 & tb3 contains Dept like: GM/ACCT/FIN
Want to delete it such that Dept is not found in tb1 & tb2
Expand|Select|Wrap|Line Numbers
  1. Delete a from t1 a left join t2 b on b.FirstName LIKE '%a.FirstName%'
  2. left join t3 c on c.FirstName LIKE '%a.FirstName%'
  3. where a.FirstName LIKE '%GM%'
  4. and b.FirstName is null and c.FirstName is Null
  5.  
Jul 1 '09 #3

P: 49
I may be clutching at straws but it may have been because I didn't put a space after using. Does this work?

Expand|Select|Wrap|Line Numbers
  1. delete a from tb1 a left join tb2 b using (Dept) 
  2. left join tb3 c using (Dept) 
  3. where (a.Dept='@dept' and b.Dept is Null and c.Dept is Null ) 
Are you storing multiple departments in a single field? This is bad database design and is making it hard to get your data back. Google "mysql normalization tutorial"(more american links :P) They will show you how to better your database design.
Jul 1 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.