December 4, 2009 at 1:07 pm
Hi,
The following query is not working. Please take a look !
Table2 has the records that needs to be deleted from table1 base upon the name field. But, Table1 has FullName and Table2 has FirstName and LastName fields respectively.
Table2 fields - (Type, RecordID, FirstName, LastName, Main)
Table1 fields - (RecordId, FullName, Main)
I am using this query:
Delete Table1 from Table1 t1, Table2 t2
where t1.Main = t2.Main
and t2.Type = 2
and t2.FirstName IN (Select FullName from Table1)
and t2.LastName IN (Select FullName from Table1)
Error: Subquery returning more than one values
Thanks.
________________________________________________________________
"The greatest ignorance is being proud of your learning"
December 4, 2009 at 1:18 pm
Try something like this:
delete T1
from Table1 T1
inner join Table2 T2
on T1.Main = T2.Main
and T2.Type = 2
and T1.FullName = T2.FirstName + ' ' + T2.LastName;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 4, 2009 at 1:23 pm
Thanks for your quick reply GSquared.
Yes, this will work. But, in the FullName field I have values like Ragan W Anderson and other values that will not be matched using concatenation operation.
Is there any way to use 'LIKE' operator here such as LIKE '%FirstName%'
Thanks,
________________________________________________________________
"The greatest ignorance is being proud of your learning"
December 4, 2009 at 1:26 pm
You could do this:
delete T1
from Table1 T1
inner join Table2 T2
on T1.Main = T2.Main
and T2.Type = 2
and T1.FullName like T2.FirstName + '%'
and T1.FullName like '%' + T2.LastName;
Put wildcards in where you need them. Might need one after LastName, if you have names that end in "Jr" or "III", etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 4, 2009 at 1:41 pm
Worked without a flaw.
I appreciate it man.
You saved me much needed time here. Was going haywire trying wildcards n stuff to figure this out.
Thanks again.
Have a very Good One !!
________________________________________________________________
"The greatest ignorance is being proud of your learning"
December 4, 2009 at 1:50 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply