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
 General SQL Server Forums
 New to SQL Server Programming
 how to delete a record with out the help of ID

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-12-20 : 07:19:51
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-20 : 07:22:10
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
Master Smack Fu Yak Hacker

2242 Posts

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

--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-12-20 : 07:49:26
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-20 : 08:01:22
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-20 : 08:04:43
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

52326 Posts

Posted - 2012-12-20 : 08:06:09
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

252 Posts

Posted - 2012-12-20 : 08:19:41
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

52326 Posts

Posted - 2012-12-20 : 08:21:42
np... Glad that you sorted it out!

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

Go to Top of Page
   

- Advertisement -