3,135 questions
Best practices
1
vote
3
replies
88
views
Deleting large data without stopping the active mysql server
I'm using a table of approximately 1TB in a MySQL database. This table also has a monthly partition. We store the last two months of data in this table and regularly truncate the data from the ...
- 21
1
vote
5
answers
102
views
Grouping rows, and then deleting only a sub range (based on their dates) from each of those groups
I use Postgres on my web server in order to record incoming queries into a table calls2, basically writing a single row each time with lots of repeating information, such as a date field ("when&...
- 12.4k
0
votes
2
answers
179
views
How to remove duplicate values that are NOT within a range from a MariaDB table?
I have a MariaDB table like this:
CREATE TABLE metric_value (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
metric_id int(10) unsigned DEFAULT NULL,
value decimal(17,2) unsigned NOT NULL,
...
- 16.6k
-1
votes
1
answer
104
views
DELETE large amounts of data in TRANSACT SQL
I have an Excel file with approximately 300k records and I want to delete these rows from a SQL Server table.
I tried using INSERT for each record but that takes way too long. I also tried to create a ...
0
votes
0
answers
24
views
Does NHibernate require bidirectional mappings for cascade delete?
If I have a very common shared table (ie. names) with a primary key (ie. name_id) included in many other tables as foreign keys, do I need my common table (names) to have a mapping reference to every ...
- 1,107
-1
votes
1
answer
58
views
How to reference the primary key to delete records based on the results of a select query using a composite primary key
Table_1 has a composite primary key: col_1, col_2.
I find records that meet a certain criteria:
select col_1, col_2
from thisTable
where col_3 = 'criteria'
Now I need to delete the records returned ...
4
votes
1
answer
236
views
Unable to delete items using Supabase Go client
I'm developing a REST API with Go and Gin framework that connects to Supabase. I've successfully implemented GET, POST, and PUT operations, but I'm having issues with the DELETE operation.
What I've ...
- 424
0
votes
1
answer
98
views
Spring boot SQL error in deleting record with not-null constraint violation
In Spring boot I have two entity classes
import java.time.OffsetDateTime;
import java.util.Set;
import java.util.UUID;
import jakarta.persistence.*;
import lombok.*;
import org.apache.commons.lang3....
- 8,058
3
votes
1
answer
110
views
How do I DELETE with WHERE and IN using a composite primary key?
My table:
create table x(
name text,
group integer,
primary key(name, group)
)
I want to delete from it by primary key:
delete from x where primary key in (["a", 1], ["a", 2]...
- 66.6k
3
votes
2
answers
120
views
Why is DELETE FROM vehicle_data WHERE NOW() > expires_at; failing in my stored function?
I have prepared a DB Fiddle for my question and I will also show my complete code below -
I am trying to store data in a vehicle_data table and each data record has an expires_at column:
-- Create ...
- 23.4k
0
votes
1
answer
63
views
Duplicate Key violation unique constraint PostgreSQL History Tables
I have table_1 and table_1_history in PostgreSQL. I have setup a trigger for insert, update, delete on table_1. So any changes in table_1 will reflect to table_1_history and will create a new row. ...
- 65
1
vote
1
answer
109
views
SQL Server Deadlock Delete Insert Statement
Both a delete and insert (range) on the same table are causing a deadlock due to contention on the same primary key index. I don't understand why.
The locks taken are RangeS-S by each process and the ...
- 65
2
votes
1
answer
67
views
different deleting OUTPUT behaviour based on source table
What I'm trying to achieve is a combined merge statement, that updates/deletes/moves data based on related metadata, somewhat like the following (MWS at the end):
merge #data as target
using #metadata ...
- 45
0
votes
2
answers
97
views
Python SQL executemany DELETE statement runs successfully without error for no matching records found
I have a file which has 100 of records to get deleted from a table. I'm using cx_oracle connect connection with executemany statement from Python to Oracle SQL and trying to delete the entire 100 ...
- 1
0
votes
1
answer
274
views
DataStage Oracle Connector doesn't execute 'After SQL statement'
I have a problem with an Oracle Connector. It should work as follows:
1 - Delete rows where the start date is equal to SYSDATE
2 - Insert data in that table
3 - Update rows with start date different ...
- 17
0
votes
2
answers
203
views
Delete / overwrite rows of data based on Matched Keys in Spark
I have 2 tables within Lake Database in Synapse (Parquet). It is running in Spark.
1 table with 10 billion rows (tableA) and the other 10 million rows (tableB).
I want to delete the 10 mill rows from ...
- 21
0
votes
1
answer
70
views
oracle - index rebuild after DML operation
I have a table with primary key on one column and another column with non unique index.
Table volumetric is around 2 mil records. No partitions in place.
delete some records (20% of data volume)
...
-5
votes
1
answer
56
views
How to remove duplicate rows in postgresql
I want to delete the duplicate rows from a table nashvillehousing. But when I try to use a CTE and delete the duplicate rows it isnt working and I get "relation cte doesnot exist",SQL state: ...
- 1
0
votes
2
answers
152
views
Window Function with multiple conditions to DELETE records from Table
This is my table:
I need to delete those records where there is NL46 ShopCode, but only if the ProductCode has ShopCode 'FR43','FR44' or 'FR45'.
I only need to keep the rows with NL46 if these ...
0
votes
1
answer
168
views
Skipping locked rows when deleting data in SQL Server
I got a table in an ERP system where lot of old data resides. I got a job which detects and deletes that outdated data.
However, some of that data is locked by the ERP system and therefore cannot be ...
- 9
1
vote
0
answers
429
views
Optimizing of a MariaDB DELETE query in a big table
I am using MariaDB 10.11 on Linux.
I have the following table that is supposed to get about 40 inserts per second and about 40 deletes per second.
It contains trade data and is used to generate stock ...
- 65
0
votes
3
answers
78
views
Executing stored procedure delete all rows from table despite WHERE clause setting
I created this little stored procedure that deletes a row from my 'qrcode' table.
Due to the constraint, I update all child rows before, it works fine.
However, performing this procedure....deletes ...
- 777
0
votes
1
answer
2k
views
Salesforce - Duplicate value found error while trying to delete a record
We are getting an error while trying to delete a record in an Apex class. I have gone through some research for this error (listed below). However, these examples talk about this error while doing an ...
- 3
0
votes
0
answers
39
views
Fetch method delete react
I need help when clicking on my icon the corresponding message deletes it
I can't get my route to work on the front side
mon code front
const handleTrash = (id) => {
fetch(`http://localhost:...
0
votes
1
answer
100
views
MySQL optimize a SQL for a table without indexes
The metadata table of Spring Batch will not be automatically cleaned,
so I tried to clean it manually (provided that other batches processes are executed normally during the cleaning process).
When I ...
- 13
1
vote
1
answer
678
views
How to parallelize SELECT and DELETE?
In PostgreSQL I have to fetch ids in small batches and delete them at same time, so that no 2 clients will fetch same ids.
Sadly, DELETE does not support LIMIT, so I came up with this query:
DELETE ...
- 3,342
-1
votes
1
answer
53
views
Delete rows from a parent table without affecting the condition table
I have a main table and a secondary one. Through a query I connect the secondary with the primary on the left, to filter the results of the primary. PhpMyAdmin disables editing and deleting the ...
- 19
-1
votes
2
answers
34
views
Deleting rows found with left join on SQL
I am using this query to show all the departments and their managers, including the departemnts with no managers, that's why I am using left join. Now I need to delete the departements where there is ...
- 11
0
votes
1
answer
147
views
Asking for advice on debugging a not-working JPA delete call
We're having a rather complex table structure in our Spring Boot application, consisting of about ten entities linked by one-to-one, one-to-many, many-to-one and many-to-many relations. They're ...
- 2,137
-2
votes
1
answer
53
views
Can't display in index.php my data table when items are deleted [duplicate]
I have a form and I connected with MySQL, so whenever I fill it up it will be shown in mySQL. The next step is to have a table of contents in my index.php, which I have. But now when I wanna press the ...
- 1
1
vote
1
answer
218
views
AFTER DELETE trigger fires only if a DELETE is executed on a db query but not on EF Core
I have two master-detail tables with a one-to-many relationship. I want to let a trigger fire after a deletion has happened, whether it has been as a cascade action (deleting the master by its PK) or ...
- 220
1
vote
1
answer
48
views
PostgreSQL doesn't take into account a DELETE in WITH before the request has ended
I have two tables to handle attachments:
"files" which contains an id and other info
"attachments" which contains the id_file (the id in the table files is the foreign key) and ...
- 2,147
0
votes
1
answer
98
views
Why Postgres does not delete rows if one of the columns is NULL when USING/IN/EXISTS is used? [duplicate]
Let's say we have a table:
CREATE TABLE rows(
a int NOT NULL,
b int,
c int
);
INSERT INTO rows(a, b, c) VALUES (1, 1, NULL), (2, NULL, 1), (3, 1, 1);
Why Postgres does not delete the row using ...
- 179
0
votes
0
answers
28
views
SQL Delete Statement taking a long time to run (oracle)
In a timesheet system, there is the charge tree where we enter projects for employees to add to the timesheet. We are wanting to start cleaning this up on old projects that have ended, etc.
This is ...
- 1
0
votes
0
answers
33
views
Deleting data from MySQL table periodically [duplicate]
We are using MySQL where we have almost around 100 tables. Data in these tables grow with time. So we need to delete the older data periodically. I think there are two approaches for this. First is to ...
- 31
1
vote
1
answer
71
views
Delete the single existing record in table OR delete all records except latest?
I'm trying to figure out a way to check for the count of records in a table, and:
if it's equal to 1, then just delete that one record,
if it's greater than 1, then delete all except the latest ...
- 6,625
0
votes
1
answer
540
views
Apex checkbox to select and delete raws in interactive report
I'm just starting with Apex,
I just need to link a delete button to the checkbox in an interactive report, in order to delete the selected raws.
This is the report source (SQL query):
select ...
1
vote
1
answer
204
views
Why DELETE after CTE fails with Syntax error?
I have a SAP HANA SQL query with two Common CTE's and followed by delete SQL on a regular Table. The SQL execution fails with incorrect syntax. Below is the error code and the HANA SQl script.
WITH ...
- 11
0
votes
0
answers
47
views
how to do insert, update and delete entries in a table in postgresql [duplicate]
id
name
Createdat
Updatedat
flag
1
A
25-12-2023 01:11:15
true
2
B
24-12-2023 12:12:12
25-12-2023 14:14:14
false
I am having table like above. I am getting set of ids along with other details as a ...
- 11
0
votes
2
answers
124
views
SQL Delete specific rows based on date and criteria [duplicate]
I've got a table that has duplicate data that needs to be cleaned up. Consider the following example:
CREATE TABLE #StackOverFlow
(
[ctrc_num] int,
[Ctrc_name] varchar(6),
[docu] bit,
...
- 15
0
votes
1
answer
64
views
Function to DELETE rows taking the parameters in sql query
I want create a function which removes rows and returns true if there are no errors:
CREATE OR REPLACE FUNCTION s_grnplm_tt.sp_del_stts_cd(num text, package_name text, stts_cd text)
returns ...
-1
votes
1
answer
126
views
Error Code: 1241. Operand should contain 1 column(s) when delete duplicate
delete from dupp
where empno in (select empno,count() from dupp
group by empno
having count()>1)
Error Code: 1241. Operand should contain 1 column(s)
what can i do to slove this ??/
i want to ...
0
votes
1
answer
1k
views
Clickhouse cluster replicas sync completion time issue
I'm trying to remove about 50 milliong of 40 billions rows table (one specific day based on event_date column) , I'm using lightweight delete with "where" clause and "where" has no ...
- 101
1
vote
1
answer
105
views
Problem with mysql DELETE from multiple tables
I was using this query:
DELETE T1, T2
FROM table1 T1, table2 T2
It seems to work inconsistency in mysql.
If both tables contain rows, then all row in each are deleted, as I expected.
But if one table ...
- 31
0
votes
2
answers
1k
views
Why have Postgres tables doubled in size after cancelling a DELETE?
I ran a simple delete query on a telemetries table to clear up some data but cancelled as it was taking really long. I noticed that after I cancelled the query some of the tables (all partitions of ...
- 959
0
votes
1
answer
67
views
Remove duplicates from every tables in db
I have a db with multiple tables. The tables have all the same structure with one column called date and a bunch of other columns with various data.
I can remove the duplicates from a single table as ...
- 453
1
vote
0
answers
53
views
Delete ROOM method is not working (JAVA) How i fix it?
My delete method is not working. Here the DAO:
@Dao
public interface TodoDAO {
@Insert
public void insert(Todo todo);
@Delete
public void delete(Todo todo);
@Query("SELECT * ...
0
votes
1
answer
125
views
Loop with many DELETEs not finishing even though the statements complete OK
I have a function that deletes rows from many tables, as much as 130+ tables, in a loop. The problem isn't the DELETE statements themselves, they seem to complete fine in a timely fashion, but for ...
- 5,043
1
vote
1
answer
3k
views
Clickhouse table data deleting
I have clustered clickhouse instance and observing following behavior: when I delete data on every node of clickhouse using for example alter table db.tb on cluster cl1 delete where event_date = ...
- 101
1
vote
1
answer
50
views
Cannot seem to delete nor add to database from my php localhost
I am building a partial website with others but when I want to delete a checked item, it gives an error
"Not Found
The requested URL was not found on the server. If you entered the URL manually ...
- 13