Delete statement does not execute in sql

Question

I read the similar issue shared on stack overflow regarding DELETE command is not execute a query.

I am a beginner level on SQL and was trying to understand identity column action so I created a super basic studentinfo table and put firstname, lastname, and id columns.

ID selected as identity column and incremental is setup 5. That is the summary of the table. And created couple rows for the table.

Now when I execute DELETE FROM StudentInfo WHERE StudentId like '0213422' returns 0 rows affected and of course not deleting that row.

Anyone tell why is that happening?

Thanks in advance

It ID is incremental:5, how come you expect to have such an id: 0213422?? BTW, there is no row with the ID: 0213422 in the table. Also, please try DELETE FROM StudentInfo WHERE StudentId like '0213422%' — Eray Balkanli
– Eray Balkanli, Commented Nov 20, 2018 at 15:17
@ErayBalkanli This not real application for any client. Just playing around so any reason to change studentid and incremental setup. By the way I added couple rows to table. I will update the above statement. Tesekkurler :) — learningsql
– learningsql, Commented Nov 20, 2018 at 15:20
@JoakimDanielson. Thanks that worked. Sorry for very simple question — learningsql
– learningsql, Commented Nov 20, 2018 at 15:25
Are you SURE you have ID set up as an IDENTITY column? If so, values would never start with a text "0". — HardCode
– HardCode, Commented Nov 20, 2018 at 15:59

Kevin Böhmer · Accepted Answer · 2018-11-20 15:26:00Z

2

You can either use:

where StudentId like '%0213422%'

or

where StudentId = '0213422'

Like can be used with placeholders like '%' or '_'

'%' - indicates that there could be multiple characters in the placeholder.

'_' - indicates that there is exactly one character as a placeholder

ID | Stuff

1 | Foobar

2 | Fobar

where Stuff like 'F%bar' --would return both row

where Stuff like 'F_bar' --would retun only the second row

answered Nov 20, 2018 at 15:26

Kevin Böhmer

4565 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GuidoG Over a year ago

Actually, like '0213422' works the same as = '0213422'. It will find just the one with the exact value '0213422'

Kevin Böhmer Over a year ago

Yes but i dont think that like '0213422' is the prefered way to use a "like" clause. I asume he didn't know how Like works, so i explained it

GuidoG Over a year ago

On that I also agree

GuidoG · Accepted Answer · 2018-11-20 15:51:13Z

0

Normally an identity column is type bigint or int and contains simply spoken just numbers. So I wonder that a value can have a leading 0.
I suggest to use a select statement first to see, what is in the result set like

Select * FROM Studentinfo WHERE StudentId like '%213422'

or

Select * FROM Studentinfo WHERE StudentId >=213422

edited Nov 20, 2018 at 15:51

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

answered Nov 20, 2018 at 15:29

Wilhelm

3061 silver badge10 bronze badges

Collectives™ on Stack Overflow

Delete statement does not execute in sql

2 Answers 2

ID | Stuff

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

ID | Stuff

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related