Questions tagged [delete]

In the database structured query language (SQL), the DELETE statement removes one or more records from a table.

Filter by
Sorted by
Tagged with
0 votes
1 answer
52 views

Routinely deleting data from tables using stored procedure

I've created a maintenance stored procedure to delete data older than 30 days on events table. What are the best practices for deleting data? I've encased the procedure with error handling, i.e. TRY......
  • 11
0 votes
1 answer
35 views

postgresql - delete duplicate object in json

I need to remove duplicated objects in a jsonb field in table in PostgreSQL 12.7. How can I do this for all rows in table? In this example I need to remove the 3th and 4th object (date: 2022-09-12) [ ...
1 vote
1 answer
26 views

Can someone explain in simple terms Cassandra nodetool commands like compact and cleanup?

Hello Everyone I am new in databases and i am trying to understand some Cassandra commands. I would like to know about the following commands : nodetool compact nodetool cleanup nodetool repair ...
0 votes
2 answers
89 views

Restrict delete statement on certain table in postgres?

I have a problem where my customers are complaining that records get deleted even though I see all id's are correct in that table, just to make sure everything is like it should be, is there a way to ...
3 votes
1 answer
103 views

Deadlock on DELETE - DELETE for parallel execution of same stored procedure

I have a stored procedure which ran parallely by two processes. Code for sp : DECLARE @inputXML XML DECLARE @tempTable TABLE (afid INT, ccid INT) INSERT INTO @tempTable SELECT ref.value('@afid', '...
-3 votes
1 answer
39 views

DELETE ... INTO ... FROM (SELECT)

I am wondering why this is coded this way. declare @messages table ([SEQUENCE_ID] BIGINT, [MESSAGE_ID] BIGINT PRIMARY KEY CLUSTERED ([SEQUENCE_ID])) ; delete T output deleted....
1 vote
2 answers
60 views

Delete but keep x amount of rows per input

Let's say I have the following table, with data: Id User Item Time 1 1 Banana D+1 2 1 Banana D+2 3 1 Banana D+3 4 2 Apple D+1 5 1 Apple D+2 6 2 Apple D+3 7 2 Apple D+4 The column definitions ...
0 votes
1 answer
166 views

Trigger execution order after delete

Let's say I have two tables, one called ParentTable, and another called ChildTable, with a one-to-many relationship, where one Parent may have many Children. I'm creating an AFTER DELETE trigger on ...
  • 139
0 votes
0 answers
198 views

Best way to manage old data in postgres

I have a postgres db where everyday a significant number of rows across multiple tables are considered outdated and soft deleted. My understanding is that there's no point in keeping these rows around ...
-3 votes
1 answer
69 views

Permanently Delete the sql server database table

I wanted the remove the table from the sql server database. The table has 500 million records. I don't need the table anymore. What's the best approach? Can I use drop table command to delete the ...
  • 55
0 votes
0 answers
10 views

Persist state on derived data structure

I have a graph in my app, that I need to save to a db. So, first thing, I created a Nodes table and Edges table: Node - id PK - name TEXT - x INT - y INT Edge - id PK - from FK REFERENCES Node - to ...
  • 101
1 vote
0 answers
90 views

Archiving Historical Data

Right now on Production, for deleting historical data, used a script which is manually run in SSMS. Script deletes batches of 100 rows from Table1 where [Date] between [Start] and [End] in a looped ...
0 votes
1 answer
202 views

Adding soft delete to a database after having used hard delete

Introduction My app collects data from a centralized source where many different users can submit data about their organisation and their staff. Previously we used to just hard delete a users data ...
  • 1
0 votes
2 answers
49 views

Oracle parent child move failed

I have two tables, a parent and child table, which have referential integrity declared between two tables and try to move both the tables so I can delete rows. I start with the child table and the ...
  • 103
0 votes
1 answer
194 views

How to implement insert, update, delete with a many-to-many relationship for tags?

I am creating an application to manage some ebooks and I would like to have tagging functionality. The best db design I have found is to be the many-to-many relationship. I know how to set up my ...
0 votes
1 answer
98 views

MSSQL Large table rolling using partitions

I have a large DB (6TB) consisting of 3 tables: “messages”, “messagesDetails” , “unprocessedMessages”. Here are simple descriptions of them: TABLE [messages]( [id] [bigint] IDENTITY(1,1) NOT ...
0 votes
0 answers
21 views

Actualizar en Trigger de delete en Mysql8

tengo una tabla en MySQL8 y una situación similar a esta: Tengo solucionado el trigger de insert, pero al querer hacer el trigger de delete (por ejemplo) me dice que no puedo modificar la tabla ya ...
0 votes
1 answer
126 views

What postgresql.conf values in Postgres 9.6 would help being altered for Large Data Deletion/Migration

I have a query which is to remove approx 418 million rows from a table into a separate table until we decide what to do with the older data. Query below being used: BEGIN; CREATE TABLE public....
  • 195
0 votes
1 answer
154 views

resolve lock wait timeout delete innodb table

In an application I work with, DELETEs are rare, though they seldom happen. When they do, they remove few rows but from several related tables, like 8 to 10. Most of the times, mainly when the ...
  • 376
1 vote
2 answers
410 views

Delete old rows from a big table, keeping one per given time interval

I am trying to delete some rows from a table in which I have saved timestamps only (data type timestamptz): The usual operation to delete from DB/table is very simple such as I want to delete all ...
1 vote
1 answer
56 views

Copying Data into another table and removing older data should I do in batches and should I remove indexes and FK constraints?

I am looking to remove older data from a table which consists of 889 million rows of data. I have a script but I am trying to make it more robust as removing approximately 418 million rows of data to ...
  • 195
0 votes
1 answer
32 views

How properly create index for a referenced table

I have a table with thousands of records with vehicles data, and this table is referenced by the table, where images are stored for these vehicles. This second table about millions of records because ...
  • 105
0 votes
1 answer
702 views

Best practice for removing old data in postgres table but retaining a copy of removed data

We currently have a table that has approx 20 columns (one of the columns being a timestamptz datatype) in our database server which has 834 million rows. This in terms of size is a big table (approx ...
  • 195
0 votes
1 answer
71 views

Use trigger on DELETE to retrieve original SQL command and user who did it

I'm trying to create an audit log to store all delete commands on some tables, on MSSQL 2012. What I need is the username that executed it and the command executed, then I can insert them on a log ...
  • 1,433
1 vote
2 answers
95 views

Is Mysql creating any temp table , when running Mysql delete query?

Server details We have one Galera cluster Mariadb with 3 nodes . On primary node database size is 200GB & diskspace size is 400GB . On other 2 secondary nodes database size is 200GB & ...
0 votes
2 answers
124 views

Removing large amounts of rows from MySQL 5.7 table + ibdata increase question

I'm working with MySQL 5.7 on Windows. I've got several tables to clean data from. Largest table's ibd file is 300GB in size and it has almost 1.5 billion lines. I need to leave about 290 million rows ...
0 votes
1 answer
121 views

Deleting records based on an offset from creation date

Basically, I have a table where I store messages for each user. Each user is allocated 1,000 messages, by 30 days. id user_id message created_at 1 12 ... 2020-01-01 16:30:45 2 45 ... 2020-01-02 16:...
0 votes
1 answer
38 views

Dropping everything in a database owned by a certain role -- not working

I want to delete everything in a certain database owned by a certain role. I've tried to do and nothing has been deleted. sudo -u postgres psql postgres=# \l List ...
0 votes
1 answer
437 views

Oracle: "table is mutating, trigger/function may not see it"

(Yes, this is one of those question again...) I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server. I have a table I1 (called t_link in my ...
  • 183
1 vote
1 answer
827 views

Delete large number of rows and change index - what steps in which order and things to watch out for

We daily collect data with multiple records per second. The table my_xpa_data has currently close to 100 Million rows and is growing fast. table size around 8 GB (used to be 4 GB a couple of days ago)...
0 votes
0 answers
167 views

MySQL mass delete from large innodb table very slow

I have a innodb table which contains more than 120million rows including duplicates. Let's name it as big_table. I fetch all the duplicates into another table(temp_big_table) which contains nearly ...
0 votes
2 answers
41 views

Deleting Billion records in a range vs exact ID lookup MYSQL

I have a database table which is around 700GBs with 1 Billion rows, the data is approx 500 GB and index is 200GB, I am trying to delete all the data that's before 2021, Roughly around 298,970,576 rows ...
1 vote
2 answers
366 views

Get a real query plan for EXPLAIN DELETE

I have a delete query that runs a long time. I try to explain it: EXPLAIN DELETE FROM matches WHERE id = 1 And I get: QUERY PLAN Delete on matches (cost=0.43..2.65 rows=1 width=6) -> Index ...
0 votes
2 answers
181 views

Keep the foreign key constraint using soft delete

I have a product table and it is foreign key for product ingredients table. Is there a way to check if it is fk referenced before making isdeleted = true without manually querying for it?
1 vote
2 answers
742 views

Deleting rows from child table without deleting from parent [closed]

I have 2 tables, connected with inheritance. I want to delete some rows from the child table. The deletion part works but it deletes rows from the parent as well: CREATE OR REPLACE FUNCTION ...
0 votes
1 answer
51 views

What does the alias after DELETE mean?

What does the alias after DELETE mean? E.g. I have a query: DELETE p1 FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p2.Id < p1.Id What does it mean DELETE p1? I understand it as follows. ...
0 votes
1 answer
20 views

SQL which removes entries with duplicate emails

DELETE FROM Person WHERE Id IN ( SELECT Id FROM Person GROUP BY Email HAVING COUNT(*) > 1 AND Id <> MIN(Id) ) The above query produces the error: You can't specify target table 'Person' ...
0 votes
1 answer
158 views

POSTGRESQL: Using Values List in Delete

Based on the PostgreSQL Documentation it seems possible to use VALUES list as argument in Insert/Select/Update. Is it possible to use it as argument in DELETE something like: delete from some_table ...
  • 181
0 votes
1 answer
68 views

Run DELETE on each INSERT to enforce row limit

Using PostgreSQL 11. Consider a table like CREATE TABLE "logs" ( "id" INTEGER NOT NULL, "userId" INTEGER NOT NULL, "timestamp" TIMESTAMP ...
  • 319
0 votes
2 answers
30 views

Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014

We are given 5 tables. Orders Order number (Primary Key) Date of order Customer ID (Primary Key for Customers table) Serial Number (Primary Key for Machines table) Customers ID ...
7 votes
3 answers
6k views

Delete rows older than x days without locking table

we have a quite big MS SQL database with millions of rows. I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application. The ...
2 votes
2 answers
4k views

MySQL: DELETE didn't shrink DB size, nor table size

In mydatabase, there is a table table1 with almost 8M row, after deleting around 7M rows from the table, the size of the DB and the table itself remains the same. Table size: +-------------------------...
  • 189
1 vote
2 answers
2k views

PostgresQL Delete Trigger does not delete row

I'm currently creating archive records for my database, where each table x has a corresponding x_archive table. My solution was to create a trigger for each table that needed replication that would ...
  • 13
1 vote
1 answer
982 views

PostgreSQL: Delete Multiple Rows with multiple Conditions from the same table

I have a dilemma that makes for sort of curious experiment to be conducted but I was wondering if it already has been done. I have a table: create table test(action_date date, item_num int, <...
  • 181
3 votes
3 answers
5k views

How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?

The below is a valid TSQL statement. But I want to understand how SQL Server processes it. DELETE A FROM table1 WITH (NOLOCK) WHERE EXISTS ( SELECT 1 FROM table2 B WITH (NOLOCK)...
  • 31
0 votes
1 answer
134 views

Postgres SQL: INSERT from DELETE

I have a table with id,user_id,timestamp and I'm trying to INSERT into the table, the user_id from a DELETE from table where id=some_number and timestamp. So what I'm trying to achieve looks like: ...
1 vote
2 answers
55 views

How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014?

For example, I have a table of: id | code | name | type | deviceType ---+------+------+------+----------- 1 | 23 | xyz | 0 | web 2 | 23 | xyz | 0 | mobile 3 | 24 | xyzc | 0 | web 4 ...
  • 11
1 vote
2 answers
663 views

How can I efficiently delete records when a counter reaches zero in PostgreSQL?

Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this ...
0 votes
1 answer
119 views

PSQL cte delete seem have have ambiguity when select same column from delete table

Both these two sql can be execute , the first one delete all , the second one is logic right. the first one with d0 as ( delete from seller_create_request where seller_id in (...
  • 139
3 votes
2 answers
210 views

Extremely bloated transaction log during delete

Now, I know that the log can get large during big deletes and one should try to chop it into batches. But I find this scenario strange and if someone could explain it to me that would be GREAT!. I ...

1
2 3 4 5
9