Delete particular - SQL Server Forums

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 particular

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2012-08-30 : 00:08:29

Trying to delete those rows which start with SB_ID:

How to write the Syntax can anyone help me

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 01:21:07

is SB_ID table column? or is it value of column?

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

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2012-08-30 : 01:30:34

SB_ID is the Value in the column

Like
SB_ID:0000000000
SB_ID:0000000001
SB_ID:0000000002
SB_ID:0000000003

Need to remove rows starting with SB_ID

DELETE FROM SHAFT
WHERE GPID IN
(SELECT GPID FROM SHAFT
WHERE GPID = ('SB_ID:%'));
GO

No Rows Deleted

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 01:40:28

[code]
DELETE FROM SHAFT
WHERE GPID LIKE 'SB_ID:%'
[/code]

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

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2012-08-30 : 01:55:16

Thanks Vishak16.

I have to include the Where condition which deletes based on the primary key.

Like

DELETE FROM SHAFT
WHERE GPID LIKE 'SB_ID:%'
AND SHAFT.RECNUM = RECNUM

I am trying this like inner join but couldn't delete

Any further help.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-30 : 03:48:16

As a sanity check could you run the following t-sql , and see what it returns?
SELECT GPID FROM SHAFT WHERE GPID = 'SB_ID:%'

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-30 : 05:26:05

What do you mean to delete based on primary key and why you need self join ???

DELETE FROM SHAFT
WHERE GPID LIKE 'SB_ID:%'
AND SHAFT.RECNUM = RECNUM

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:28:30

quote:
Originally posted by dbalearner

Thanks Vishak16.

I have to include the Where condition which deletes based on the primary key.

Like

DELETE FROM SHAFT
WHERE GPID LIKE 'SB_ID:%'
AND SHAFT.RECNUM = RECNUM

I am trying this like inner join but couldn't delete

Any further help.

there's no other table involved

so you dont need extra condition at all

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

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2012-08-30 : 18:07:21

Perhaps I am after the Self join like Inner Join which makes correct if I do so.
There are 3 criterias:

The Problem was there were COLUMN VALUES which involves 'SB_ID:' at - Start, and in the middle and also in the end. ( for which I have to generate the query)

Also I need to check there is referential integrity which is to check on RECNUM and I have included:
AND SHAFT.RECNUM = RECNUM ( This is done)

Lastly, I have to check the same referential integrity 'RECNUM' for another schema table which has got the same RECNUM and this should also needs delete and I have specified this based on the second statement.

Though it seems very simple but this is derived of the table where there is no value with SB_ID: included and needs this to be added at the first instance and include the DELETE for a 'condition' when occurs.

Thanks All, wonderful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 18:11:29

if its referential integrity check with respect to another table its fine. But what you had was kind of trivial or redundant comparison between same column which doesnt make any sense

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources