• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Help with update,add, delete records

New Here ,
Dec 26, 2006 Dec 26, 2006

Copy link to clipboard

Copied

Hi every one,

This is a problems that I have been trying to figure out for a little while now and I hope you all can help me out. So this is the situation.

I am useing CFX XLS 2 Query to create a query from an excel doc I have. Now my problem is how can I loop through and delete the records that no longer are in the spreadsheet?

So in other words my client will be uploading a new XLS each day, this xls will have some new data, some of the same and some will be gone. So I need the system to check and see if the stocknumber is there, if it is it will just update that record, now if the record is new it will add the record but for the life of me I can't figure out how it can delete record.

I had thought maybe doing a select where the stock numbers dont match but that didnt work out too well :)

Thanks for any help you may be able to provide
TOPICS
Advanced techniques

Views

235

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Dec 26, 2006 Dec 26, 2006

Copy link to clipboard

Copied

You could consider using ValueList() to create a list of stocknumbers from your excel query, then do something like this:

<cfset sCurrentStockNumbers = ValueList(myQuery.stocknumber)>

<cfquery name="removeStockNumbers" datasource="myDataSource">
DELETE from myTable
WHERE stocknumber NOT IN (<cfqueryparam value="#sCurrentStockNumbers#" list="Yes">)
</cfquery>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 26, 2006 Dec 26, 2006

Copy link to clipboard

Copied

LATEST
Hmm it seems to make the list but its deleting all the records now lol thanks for you help

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation