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
-3 votes
1 answer
52 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 ...
user avatar
  • 55
0 votes
0 answers
8 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 ...
user avatar
  • 101
1 vote
0 answers
87 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 ...
user avatar
0 votes
1 answer
59 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 ...
user avatar
  • 1
0 votes
2 answers
36 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 ...
user avatar
  • 103
0 votes
1 answer
72 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 ...
user avatar
0 votes
1 answer
73 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 ...
user avatar
0 votes
0 answers
15 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 ...
user avatar
0 votes
1 answer
78 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....
user avatar
  • 143
0 votes
1 answer
80 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 ...
user avatar
  • 376
1 vote
2 answers
177 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 ...
user avatar
1 vote
1 answer
44 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 ...
user avatar
  • 143
0 votes
1 answer
29 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 ...
user avatar
  • 105
0 votes
1 answer
300 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 ...
user avatar
  • 143
0 votes
1 answer
57 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 ...
user avatar
  • 1,413
1 vote
2 answers
72 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 & ...
user avatar
0 votes
2 answers
53 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 ...
user avatar
0 votes
1 answer
39 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:...
user avatar
0 votes
1 answer
29 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 ...
user avatar
0 votes
1 answer
243 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 ...
user avatar
  • 173
1 vote
1 answer
236 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)...
user avatar
0 votes
0 answers
124 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 ...
user avatar
0 votes
0 answers
129 views

Postgres jsonb field: remove an element from an array by the element id

Figure a Postgres 12.5 jsonb field containing the following: [ {id:1, name: 'one'}, {id:2, name: 'two'}, {id:3, name: 'three'} ] How to write the request that will match: "drop the object ...
user avatar
  • 197
0 votes
2 answers
32 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 ...
user avatar
1 vote
2 answers
130 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 ...
user avatar
0 votes
2 answers
147 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?
user avatar
1 vote
2 answers
494 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 ...
user avatar
0 votes
1 answer
34 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. ...
user avatar
0 votes
1 answer
19 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' ...
user avatar
0 votes
1 answer
33 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 ...
user avatar
  • 181
0 votes
1 answer
58 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 ...
user avatar
  • 289
0 votes
2 answers
28 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 ...
user avatar
5 votes
3 answers
4k 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 ...
user avatar
2 votes
2 answers
2k 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: +-------------------------...
user avatar
  • 179
1 vote
2 answers
1k 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 ...
user avatar
  • 13
1 vote
1 answer
697 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, <...
user avatar
  • 181
3 votes
3 answers
3k 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)...
user avatar
  • 31
0 votes
1 answer
80 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: ...
user avatar
1 vote
2 answers
49 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 ...
user avatar
  • 11
1 vote
2 answers
480 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 ...
user avatar
0 votes
1 answer
97 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 (...
user avatar
  • 139
3 votes
2 answers
185 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 ...
user avatar
1 vote
0 answers
477 views

Manually delete AUTOBACKUP of controlfile

My boss has decided to change retention policy to a 31 days period instead of redundancy of 4 copies. One of the side effects is that autobackups get obsolete after 31 days. This is the configuration ...
user avatar
  • 11
0 votes
2 answers
78 views

Deleting all the content from the database

We want to clean a database (delete all the records) with around 25 tables and a size of around 650+ GB in production. We are currently using MS SQL 2016 Standard Edition with AlwaysON HA. Is ...
user avatar
  • 43
0 votes
1 answer
84 views

Logical Backup strategy in large Oracle Databases

in Oracle databases, I would like to know if it is advisable to make logical backups with exp or expdp in large databases with large volumes of data, greater than terabyte, I have seen several systems ...
user avatar
0 votes
5 answers
3k views

Disadvantages to using ON DELETE CASCADE on every foreign key?

Our application currently has "soft delete" built-in to most tables with a BIT "deleted" column. There is now a requirement for "hard delete" functionality. Are there any ...
user avatar
0 votes
0 answers
798 views

Deleting child record doesn't delete foreign key reference

I am using PostgreSQL 10.7. I have this strange occurrence where I fail to see the problem. Whenever I delete all child-records referring to a parent, somehow the reference between these two tables ...
user avatar
0 votes
1 answer
433 views

Delete rows on a table with cascading foreign keys

I want to delete rows on Table A but it has foreign keys on Table B and Table D. And Table B has foreign keys in Table E and Table D has foreign key in table F. Can I delete all the cascading records ...
user avatar
1 vote
1 answer
66 views

Delete duplicate records and update the mapping table

I have store_table_map and item_price_map. I want to delete duplicate records in store_table_map and retain the latest row based on updated_date because i want to put constraints in store_id and ...
user avatar
0 votes
1 answer
436 views

after deleting 100 millions of records in postgresql free space doesn't change

Recently I configured netflow for monitoring traffic of my company. I use pmacct to collect netflow packets and PostgreSQL for storing them. I only keep netflow records for a week and after that every ...
user avatar
  • 1

1
2 3 4 5
9