Solved

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

Posted on 2014-09-21
5
158 Views
Last Modified: 2014-09-26
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!
0
Comment
Question by:brihol44
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40336036
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
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40336624
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
 

Author Comment

by:brihol44
ID: 40336674
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 40337269
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
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40337284
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to base a filter depending on fields contents? 15 57
SQL Server Update Query Streamline 4 28
Access 2016 - query 23 59
Sql Server group by 10 26
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question