Delete statement

Question

Post reply

Delete statement

Bron Tamulis

Ten Centuries

Points: 1090
More actions
June 1, 2012 at 11:05 am

#274976

I have to delete 401 records from a data table. The 401 records have a common primary key and 401 sequential unique fields.
Example:
SOPNUMBE DEX_ROW_ID
XXX1234 001
XXX1234 002
XXX1234 003
XXX1234 004
thru
XXX1234 401
My delete statement would be:
delete sop10200 where sopnumbe = 'XXX1234' and dex_row_id in (001,002,003 etc)
my question is - is there any sql syntax that allow something like:
delete sop10200 where sopnumbe = 'XXX1234' and dex_row_id in (001 thru 401)
or do I have to specify the 401 records?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

how about:

delete sop10200 where sopnumbe = 'XXX1234' and (dex_row_id >= 001

and dex_row <= 401)

or

delete sop10200 where sopnumbe = 'XXX1234' and dex_row_id between 001 and 401

And then again, I might be wrong ...
David Webb

Sigerson Hall of Fame Points: 3697 More actions · Answer 2

@celko:

I thought David Webb's reply was very helpful and is exactly what I would have said.

On the other hand, yours focused on the rules for how to ask a question that you might deign to answer.

I appreciate your own demonstrated level of expertise, but Bron's question was clear enough and showed that he had spent some time considering alternatives and trying to help us help him. So, beyond demonstrating your love of rules and regulations, you added nothing that David Webb hadn't already said in his post.

IMHO, dissing Bron for not showing 'minimal polite behavior on SQL forums' was out of line. Let's all keep this friendly, okay?

Have a nice day.

Sigerson

"No pressure, no diamonds." - Thomas Carlyle

Jared SSC Guru Points: 61793 More actions · Answer 3

David Webb-200187 (6/1/2012)
how about:
delete sop10200 where sopnumbe = 'XXX1234' and (dex_row_id >= 001
and dex_row <= 401)
or
delete sop10200 where sopnumbe = 'XXX1234' and dex_row_id between 001 and 401

The problem here is that you are treating dex_row_id as a number. Without the DDL from the OP, I can only assume that this is a VARCHAR field based on the 001 instead of 1. That being said, you could convert... Is there another column you can use for this delete? I believe that this is a Microsoft Application, Dynamics maybe?

Jared
CE - Microsoft

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 4

When I look at Celko's response, I did see he eventually got to the supplying an SQL statement that had FROM in it.

I could see the poster putting forth some effort - which is important in learning.

As a side note to the Original Poster -

You may want to experiment with a select statement, just to learn.

IN ( ) and Between, as well and >= / <= can have performance implications at times.

So you might not always use the same one.

Jared SSC Guru Points: 61793 More actions · Answer 5

So, going back to my Great Plains days, I realize this is a sales table.

1. Why are you deleting data from a Great Plains table when you don't truely understand SQL? (not trying to insult, just saying that this is very dangerous and you must have a reason)

2. dex_row_id is an int column, so it should not be 001, but 1.

In that case, use this first:

SELECT *

FROM sop10200

WHERE sopnumbe = 'XXX1234'

AND dex_row_id BETWEEN 1 AND 401

If this returns the data you want to delete, then run this:

DELETE

FROM sop10200

WHERE sopnumbe = 'XXX1234'

AND dex_row_id BETWEEN 1 AND 401

Jared
CE - Microsoft

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 6

Jeff -

Pretty cool to meet someone else who has worked with Great Plains. My client has a user who's system locked up while posting a sales invoice. The invoice subsequently got moved into the historical sop30200 and sop30300 tables while 'remaining' in the SOP10100/sop10200 (open) tables. This violates the design of GP.

I confirmed this by using a SQL script to query the entire database for a specific string value (used the invoice number) and quickly determined the problem.

I posted in this forum after fixing the data problem on the client's database (copied LIVE into TEST - ran my delete (used in with all dex_row_id numbers separated by comma) - confirmed with user interface testing and rerunning sql script). Then repeated in LIVE. Took some time but I'm always overly cautious and concerned with deletes and update statements.

As an application consultant supporting GP I have no choice but to dally on occasion into the world of SQL. This forum has been tremendously useful in the past - I try to respect all the contributors by first searching the forum and SQL BOL for solutions before posting.

My original post was more of a 'see if I can learn something new' intent. I knew SQL has >= and Between (used many times on select statements) but I didn't know if I could apply it to a delete statement.

In my role - SQL is very challenging because 1 day I am totally immersed in it - the next day I am not touching it.

I actually have done many SQL views, Triggers, Stored Procedures, etc......many with the assistance of contributors to this forum, definitely not as eloquent as the contributors in the forum - and I admit I'm definitely not a contributor due to my limited knowledge. I wish I had the time to study and become a contributor.

I hope I can continue to post and continue to grow my knowledge of SQL. I've always been a little uneasy taking knowledge from contributors of this forum, I never understood nor have been told what the etiquette is.....does a person such as myself (paid GP consultant) have the right to use contributors from this forum to solve my work problems?

I would appreciate and certainly respect any opinions in this matter.

Thanks......

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 7

Sorry Jared - I called you Jeff. In the reply screen I can't see previous psotings - I was goping by memory - many apologizes......

Jared SSC Guru Points: 61793 More actions · Answer 8

I don't see any issues in taking what you learn here and applying it to your own business. In fact, that is what most people here take away from the forums. Me, I use the forums to increase my knowledge and keep sharp on things since I am in a DBA role instead of a developer at my current employer. However, you come here asking a question and I see no reason why someone would expect you to NOT use their answer for your job.

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

As long as we aren't doing your work for you, not a problem to come and ask questions and get help where yo are struggling. The key is you are trying to learn and become better yourself.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 10

CELKO (6/3/2012)
If you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905
It is very good and will help you learn the correct terms and basic syntax.

Heh... and unlike certain author's books, there isn't a single condescending remark in the whole book. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Webb-CDS SSCoach Points: 17398 More actions · Answer 11

And just to clarify...

It's always better if, when asking questions, you post DDL for all the objects concerned, some sample data in a form that will be easily inserted into the objects created by the DDL, and the output you expect from the data you have provided. This doesn't have anything to do with politeness, but it will speed the resolution of your problem and reduce the frustration of the people who are trying to help.

Minimal politeness would be asking for help rather than demanding and saying thank you to everyone who came to your aid, whether they could solve the problem or not. I'd say you scored well on both of those.

Using ISO formats that most people don't actually use and converting the t-sql we all know and love to sanitized ANSI syntax is not required, and in some cases can mask the problem you are trying to solve if involves a vendor specific mutation. We all understand t-sql and assume that, if you had some generic 100% ANSI compliant database you'd be posting on http://www.doesntexist.com.

Just my 2 cents...

And then again, I might be wrong ...
David Webb