Deletion with LIKE Variable

Question

Deletion with LIKE Variable

melon.melon

SSC Enthusiast

Points: 106
More actions
July 1, 2009 at 7:39 pm

#73232

Hi,
Before FirstName = Harbour Twain in T1 is deleted, it has to check that FirstName = Harbour Twain is not found in T2 & T3. If exist, deletion cant be perform, else it will do the deletion.
I have 3 Table (T1,T2,T3) :
T1
FirstName
--------------------------------------------------
Twain
Harbour Twain
Mark Barclay
Sammy
O'Neill
Rachel
T2
FirstName
--------------------------------------------------
Twain/Mark Barclay
Twain/Sammy/Mark Barclay
null
T3
FirstName
--------------------------------------------------
Mark Barclay
Twain
Harbour Twain/Mark Barclay/Sammy/O'Neill/Rachel
Anybody can help me with this query which will be inside a Stored Prod:
Delete a from t1 a left join t2 b on b.FirstName LIKE '%'+a.FirstName+'%'
left join t3 c on c.FirstName LIKE '%'+a.FirstName+'%'
where a.FirstName='Harbour Twain'and b.FirstName is null and c.FirstName is Null
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

melon.melon SSC Enthusiast Points: 106 More actions · Answer 1

i guess i tried to put "a" as a field in the column of T1 and not inside T2 & T3, it doesnt allow any form of deletion from the query

Delete a from t1 a left join t2 b on b.FirstName LIKE '%'+a.FirstName+'%'

left join t3 c on c.FirstName LIKE '%'+a.FirstName+'%'

where a.FirstName='a' and b.FirstName is null and c.FirstName is Null

but when i tried this it give rows with results Twain, Mark Barclay,etc.

select * from T2 where FirstName LIKE '%a%'

ksullivan SSCrazy Points: 2346 More actions · Answer 2

The first code you posted does work according to your requirements. From the sample data you posted there are no records in T1 that can be deleted. All of the names are in either T2 or T3. Add someone who is not in T2 or T3 into T1 and he will be deleted.

create table t1 (FirstName varchar(50), id int identity(1,1))

create table t2 (FirstName varchar(50), id int identity(1,1))

create table t3 (FirstName varchar(50), id int identity(1,1))

insert into T1

select 'Twain'

union select 'Harbour Twain'

union select 'Mark Barclay'

union select 'Sammy'

union select 'O''Neill'

union select 'Rachel'

insert into T1

select 'NotADupe' -----<<<<<

insert into T2

select 'Twain/Mark Barclay'

union select 'Twain/Sammy/Mark Barclay'

union select null

insert into T3

select 'Mark Barclay'

union select 'Twain'

union select 'Harbour Twain/Mark Barclay/Sammy/O''Neill/Rachel'

The non-dupe record is deleted with this

Delete a

-- select a.*

from t1 a left join t2 b on b.FirstName LIKE '%'+a.FirstName+'%'

left join t3 c on c.FirstName LIKE '%'+a.FirstName+'%'

where a.FirstName='NotADupe' and b.FirstName is null and c.FirstName is Null

This one does not delete anything because no records in T1 have FirstName = 'a'. As you say, there are plenty of names that have 'a' (ie where a.FirstName like '%a%'), but none can be deleted according to your specs.

Delete a

-- select a.*

from t1 a left join t2 b on b.FirstName LIKE '%'+a.FirstName+'%'

left join t3 c on c.FirstName LIKE '%'+a.FirstName+'%'

where a.FirstName='a' and b.FirstName is null and c.FirstName is Null

melon.melon SSC Enthusiast Points: 106 More actions · Answer 3

Thanks for explaining this to me, i was quite confused as a sql newbie lol =)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Try this:

insert into T1

select 'Sam' -----<<<<<Not a dupe

insert into T1

select 'Ammy' -----<<<<<Not a dupe

_____________
Code for TallyGenerator