SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to delete a record with out the help of ID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 12/20/2012 :  07:19:51  Show Profile  Reply with Quote
hello all,

i am trying this from morning but not able to understand is this scenario possible or not ???
i have a table names users

names stname
AB001 XYZ
2AB001 XYZ
AB002 AAA
2AB002 AAA

here i need to delete exclusively the records which starts with 2 like 2AB001 in this table

is this possible to delete like this one with in the row need to pick first character and delete ??suggest me

P.V.P.MOhan

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/20/2012 :  07:22:10  Show Profile  Reply with Quote
Run this code to see what you will be deleting.
SELECT * FROM tbl WHERE names like '2%'
If you are satisfied that those indeed are the rows you want to delete, then do delete like this:
DELETE FROM tbl WHERE names like '2%'
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 12/20/2012 :  07:39:57  Show Profile  Reply with Quote
Duplicate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181540

--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 12/20/2012 :  07:49:26  Show Profile  Reply with Quote
hey sunitha and chandu i tried with above queries there showing the same thing not deleting anything...

declare @frogs table (names Varchar(100),stname varchar(10))
Insert into @frogs values ('AB001','XYZ')
Insert into @frogs values ('2AB001','XYZ')
Insert into @frogs values ('AB002','AAA')
Insert into @frogs values ('2AB002','AAA')
select * from @frogs

delete from @frogs where LEFT(names,1) = '2'
delete from @frogs where names like '2%'


P.V.P.MOhan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 12/20/2012 :  08:01:22  Show Profile  Reply with Quote
declare @frogs table (names Varchar(100),stname varchar(10))
Insert into @frogs values ('AB001','XYZ')
Insert into @frogs values ('2AB001','XYZ')
Insert into @frogs values ('AB002','AAA')
Insert into @frogs values ('2AB002','AAA')
select * from @frogs

delete from @frogs where LEFT(names,1) = '2'
delete from @frogs where names like '2%'

SELECT * FROM @frogs
GO


Now execute and see results

EDIT: In your code, you are selecting results before DELETE operation. thats why u didn't see difference
--
Chandu

Edited by - bandi on 12/20/2012 08:02:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/20/2012 :  08:04:43  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

hey sunitha and chandu i tried with above queries there showing the same thing not deleting anything...

declare @frogs table (names Varchar(100),stname varchar(10))
Insert into @frogs values ('AB001','XYZ')
Insert into @frogs values ('2AB001','XYZ')
Insert into @frogs values ('AB002','AAA')
Insert into @frogs values ('2AB002','AAA')
select * from @frogs

delete from @frogs where LEFT(names,1) = '2'
delete from @frogs where names like '2%'


P.V.P.MOhan



works fine for me



declare @frogs table (names Varchar(100),stname varchar(10))
Insert into @frogs values ('AB001','XYZ')
Insert into @frogs values ('2AB001','XYZ')
Insert into @frogs values ('AB002','AAA')
Insert into @frogs values ('2AB002','AAA')
select * from @frogs 

delete from @frogs where LEFT(names,1) = '2'
delete from @frogs where names like '2%'

select * from @frogs

output
--------------------------------

before

names	stname
-------------------------
AB001	XYZ
2AB001	XYZ
AB002	AAA
2AB002	AAA

after

names	stname
---------------------
AB001	XYZ
AB002	AAA




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/20/2012 :  08:06:09  Show Profile  Reply with Quote
it may be that you've unprintable characters like spaces in data. try this too


declare @frogs table (names Varchar(100),stname varchar(10))
Insert into @frogs values ('AB001','XYZ')
Insert into @frogs values ('2AB001','XYZ')
Insert into @frogs values ('AB002','AAA')
Insert into @frogs values ('2AB002','AAA')
select * from @frogs 

delete from @frogs where LEFT(LTRIM(names),1) = '2'
delete from @frogs where LTRIM(names) like '2%'

select * from @frogs



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 12/20/2012 :  08:19:41  Show Profile  Reply with Quote
yeah i did a silly mistake. i thought those are 2 statements and seperated them and executed single
time thats the reason why i am wrong....sorry for nagging u guys with my sillyness

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/20/2012 :  08:21:42  Show Profile  Reply with Quote
np... Glad that you sorted it out!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000