Can I delete a entire grouping of rows based off a list of ids?

I'm passing a data value of .... id=1,2,4 to my coldfusion Ajax/cfc function based off a checkboxes marked.

My test data is something like this...

id    cat_id   date
1      1          10-10-1
2      1          10-10-1
3      2          10-10-1
4      1          10-10-1
5      1          10-10-2

I would like it so each time I select the checkboxes that the rows then delete that are not listed in my valuelist i'm passing grouped by date.  I figured a long drawn out way but is there a more streamlined way without writing a few sql statements?

id number 3 should be deleted from the above list so my dataset would then be....

id    cat_id   date
1      1          10-10-1
2      1          10-10-1
4      1          10-10-1
5      1          10-10-2

Thanks for any insight!
brihol44Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
CF surely has some way to do this "easily", in SQL I would use

WHERE yourfield NOT IN ( .... )

using the function described in this article :
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
0
Gurpreet Singh RandhawaCEOCommented:
well in cf:

The way you can delete is like this:

<cfquery name="delrecords">
selete from mytable where id in (<cfqueryparam cf_sql_type="cf_sql_numeric" value="#id#" list="yes">)
/cfquery>

Open in new window

the above cfquerypatam list attribute will make you are passing a numeric list by comma separator.

check the Type Syntax in adobe Livedocs while using

Cheers
0
brihol44Author Commented:
Thx! I think that would work for other cases but I'm wanting to only check against existing ids that are passed and delete the others that don't exist in the passed list but grouped by the date of those values.. So id=1,2,4 are passed but I'd 3 was not. Ids 1.2.3,4 are all apart of the same date of groupings. I thought maybe there was a sub query that could do it in one wiry statement but I'm not sure how to.
0
_agx_Commented:
EDIT:

> So id=1,2,4 are passed but I'd 3 was not. Ids 1.2.3,4 are all apart of the same date of groupings

There's 2 options:

1. Simplest is to figure out which boxes are NOT checked with javascript. Then pass those id's to the action page, ie  idsToDelete=3

DELETE FROM YourTable
WHERE   YouIDColumn IN 
              (
              <cfqueryparam value="#FORM.idsToDelete#" cfsqltype="cf_sql_integer" list="true">
              )

Open in new window


2. Otherwise, you'd need to tell the db what you mean by "groupings". For example, if you want to delete all records for a specific date - ONLY - then you need to pass a date value - along with your list of id's.

       dateValue=2014-10-01
       id=1,2,4

Then use the date value in your query along with the as Angel described. I don't know the data types of your db columns, so adjust the cfsqltypes as needed.

<!---
         Remove all records for the specified date that are NOT in #FORM.id# list.
--->
DELETE FROM YourTable
WHERE   YourDateColumn = <cfqueryparam value="#FORM.dateValue#" cfsqltype="cf_sql_date">
AND        YouIDColumn NOT IN 
              (
              <cfqueryparam value="#FORM.id#" cfsqltype="cf_sql_integer" list="true">
              )

Open in new window



That said ... this UI sounds a little confusing. Typically a UI lets users check off what *should* be deleted, rather than "delete everything that is NOT checked"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gurpreet Singh RandhawaCEOCommented:
before you query, you can just the following function of list

cflib

http://cflib.org/udf/IsListInList

http://cflib.org/udf/listCompare

http://cflib.org/udf/listCountItemSimilar

http://cflib.org/udf/listCountListInList

you can get the one which fits your needs and then you can store that value in a variable and paass that variable to the database
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.