asked on

How to DELETE multiple rows of data for a SINGLE RECORD based on coded DATE field that is found within ONE of the Rows?

My data looks like this:

rowid,detaildata
1,0BATCH NEXTGEN NEXTGENEMR 20160829CPM 651
2,1P0007.63579.0000 1609168541 899972053 1230 CN25
3,2P201608232016082399348 1 0 1
4,51 000.00
6,71609168541
7,1P0007.63512.0000 1093765679 100458751 1230 CN25
8,2P201508262015082699347 1 0 1
9,51 000.00
10,71093765679
11,1P0014.236945.0000 1174807291 2254912701 1230 GW23
12,2P201608252016082599341 1 0 1
13,51 000.00
20,71174807291
21,1P0003.316952.0000 1962840793 A00199273 3230 MM05
22,2P201608262016082699307 1 0 1
23,51 000.00
25,71962840793
26,1P0003.316921.0000 1821384934 A00188972 3230 MM05
27,2P201608282016082899308 1 0 1
28,51 000.00
30,71821384934

I need a SQL Script that will delete multiple rows of data that pertain to a SINGLE RECORD. A single record consists of rows where the "DetailData" field starts with a the numbers (1-7).

In my sample data RowIDs 2-6 are for one record. RowIDs 7-10 are for the next record. RowIDs 11-20 are for the next record..and so on..etc.

The "DetailData" field for all rows that start with a "2" contain a coded date like this "20150826" in characters 3-10.

I need to find all ROWIDs that contain a coded Date that is LESS THAN < '10/01/2015' and then DELETE all of the Rows that pertain to this RECORD. So in my example data above, I need to remove rows 7-10 out of this table since they belong to a RECORD that has a date that is LESS THAN < '10/01/2015'.

Please let me know how I can delete multiple rows of data based on this type of coded date that is found in ONE of the records ROWS.

Thanks

chaau

your data is stored in a very weird format. I am pretty sure there is an academic term for this data structure, and even an article describing pros and cons.

I think, if you have control for this database you could improve it like this: add two columns: recordId and DetailType. Then use the same RecordID for the rows corresponding to each logical record. The detailType is something that will let you identify what the detail row means. E.g you know that the detail with 1 means and order date, detail 2 means status, detail 3 means invoice amount, etc. Therefore, your table can be re-written like this:

rowid  detaildata  (truncated for display purposes)                                   recordID                      detailType
------------------------------------------------------------------------------------------------------------------------------
2      1P0007.63579.0000                           1609168541                         1234                          status
3      2P201608232016082399348            1   0         1                             1234                          orderDate
4      51 000.00                                                                      1234                          invoiceAmount
6      71609168541                                                                    1234                          tax
7      1P0007.63512.0000                           1093765679                         1235                          status
8      2P201508262015082699347            1   0         1                             1235                          orderDate

Open in new window

This way it will be more EAV table then yours. It will still be EAV though with all the problems EAV presents, but it will be more manageable this way

Marcus Aurelius

ASKER

Experts,

PLEASE. I don't need ideas on how to change my current table. It is what it is and I have NO CONTROL over the format of the source table. It is a 3rd party software that can't be changed at all. The final table that I provide back to the Source MUST be in the EXACT SAME FORMAT as the original source data...

I need to know how to accomplish my task as mentioned in my question....

Megan Brooks

Got the "is what it is" part. Nobody would query data in this form if they had a choice! Give me a few...I'm trying to think of the implicit relationship that will turn this relational, and I am still working on my morning coffee. Thank you for the morning wake-up exercise.

ASKER CERTIFIED SOLUTION

Megan Brooks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marcus Aurelius

ASKER

MEGAN...!!

GREAT WORK!... This appears to be exactly what I'm needing.

Thanks for your help it is MUCH appreciated!!

Thanks
M

Marcus Aurelius

ASKER

GREAT WORK Megan!!

Megan Brooks

You are welcome.

Having had breakfast, I am scratching my head over what I wrote for the DELETE clause, but I did test it before posting and it did do what it was supposed to do. But a delete against a CTE? I would normally delete against the base table and join to the CTE.

Marcus Aurelius

ASKER

Don't worry about that. I fully realize that you don't fully understand the exact requirement. What you provided is exactly what I needed...and I'll make the needed tweaks to make if fit my overall solution.

Thanks!
M

Megan Brooks

Great, that's what I was hoping for.

For queries like this I often write a series of CTEs, expecting that SQL Server will combine them and create a execution pipeline. I find the CTE syntax easier to read than subqueries, and it is rather easy to debug as well. Off the top of my head, without looking, I don't know what it does with the correlated subqueries, though. In my ideal world it would spool some of the extracted data to tempdb and use it for lookups. One can dream.

CROSS APPLY might be useful here, but I didn't think of it at the time and it wouldn't necessarily be more readable or produce a better plan. One way or another the query needs to do correlated subqueries to establish the relationships.