How do I quickly update my data in a collection?

Ask the community
,
1

I have data in my collection that are often times 1000+ lines long. The data is also updated weekly or daily. I want to delete all the previous data in the collection, and repopulate it with the new data that I get. I don’t want to delete the entire collection, just the data inside.

Currently, I am manually selecting multiple items at once using Shift and deleting them. Once it is clear, I then import the data in.

Is there a quicker way to do this? Also, is there a spot in the developer where I can write SQL code?

2

You can’t write sql code but you can use ‘wix-data’, then you can insert, update, or delete rows or specific cells from your collection without delete all the collection.
let me know if you need more help

3

Hi :raised_hand_with_fingers_splayed:

There’s a way, but it’s long and quite complicated. Query the database with field that holds the creation date.

let now = new Date();
let duration = 30 * 8.64e+7; // The number of days multiplied by milliseconds in a day
let value = Number(now.getTime() - duration);

wixData.query('collection').lt('_createdDate', value).limit(1000).find().then(async(result) => {
    let items = result.items;
    
    while (result.hasNext()) {
        let newItems = await result.next();
        items = items.concat(newItems.items);
    }
    
    // Remove items
    await wixData.bulkRemove('collection', items).then(() => {
        console.info(`${items.length} old items were deleted`);
    }).catch(err => console.error(err))
})

CAUTION: Be careful when deleting items, this process cannot be undone, try the code on a test collection with fake data.

Hope this helps~!
Ahamd

4

So, does this delete all the data in a collection? Does writing this code delete it just once?

5

@covid19schooltracker It does not delete all data, it deletes data older than 30 days, if you want to delete everything, use the truncate( ) function, you must be the site OWNER to run this function or run it on the backend to bypass the permissions.

Yes, this code runs only once. You can use the Job Scheduler to run functions periodically.