Delete with like variable using left join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete with like variable using left join

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-01 : 02:24:44

Hi,
When FirstName = Harbour Twain in T1 is deleted, it has to check that FirstName = Harbour Twain is not found in any of row in T2 & T3.
I have 3 Table (T1,T2,T3) like this separate by "/" in 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

The query deletes without checking whether ID is in T2 or T3:


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 LIKE '%@firstName%'
and b.FirstName is null and c.FirstName is Null

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-01 : 03:14:27

Trigger?

AMITOFO+AMEN+YA ALLAH Hope the query works

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-01 : 04:31:04

Not so sure how trigger can be done on the actual table T1 to get the results. can i put trigger in SP to work or how do i constraint T1 with Trigger to work with the delete in stored prod ?

Trigger cant be included into store procedure..it is perform to constraint before or after a table is created..
however this is inside a store prod and mayb not work though this
query will check FirstName in t1 but not in T2 & T3 before deletion.


	BEGIN TRANSACTION
IF EXISTS (SELECT * FROM t2 b, t3 c WHERE b.FirstName NOT LIKE '%@firstName%' and c.FirstName NOT LIKE '%@firstName%')
	BEGIN
	Delete from t1 where FirstName=@firstName
End
End

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-01 : 05:09:06

[code]DELETE t1
FROM t1
WHERE NOT EXISTS
(SELECT
FROM (SELECT FirstName
FROM t2
UNION
SELECT FirstName
FROM t3)t
WHERE t.FirstName = 'Harbour Twain')[/code]
Not sure can help you...so sorry

AMITOFO+AMEN+YA ALLAH Hope the query works

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 05:52:43

Have you tried
LIKE '%'+a.FirstName+'%'

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-01 : 06:11:14

just tried this and it gives "Invalid Column name"


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 LIKE '%'+Harbour Twain+'%'
and b.FirstName is null and c.FirstName is Null

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 06:16:32

LIKE '%'+Harbour Twain+'%'
this is not correct.
Harbour Twain is not a column.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-01 : 06:32:28

This query doesnt check that FirstName in t1 is not in t2 & t3 before deletion, any idea?


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

This query can check but cant be used in the left join:

SELECT * FROM t2 b, t3 c WHERE b.FirstName 
NOT LIKE '%'Harbour Twain' and c.FirstName NOT LIKE '%'Harbour Twain%'

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-01 : 23:14:14

Thank ya for the help, its fine i got the query above =)

Anyway if anybody comes to know how to make an exact check from T1 with T2 & T3.
Tried to place "a" into T1 and see if it checks with T2 & T3 for exact "a" but it checks with other fields which contains/like "a",
thats why it doesnt allow the deletion of "a" in T1 though T2 & T3 doesnt have exact "a" as FirstName.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources