FAQ Database Discussion Community


SQL find and delete repeated items

sql,postgresql,sql-delete
I am using PostgresSQL. I have following structure of my gps points table: As you can see points with id = 551071 and 551073 and 551075 have the same coordinates. I want to delete them, all that 3 points. But the problem is that I have a dataset of 600K...

Deleting duplicates in a time series

sql-server,duplicates,time-series,sql-delete
I have a large set of measurements taken every 1 millisecond stored in a SQL Server 2012 table. Whenever there are 3 or more duplicate values in some rows that I would like to delete the middle duplicates. Highlighted values in this image of sample data are the ones that...

Delete query not working sqlite

android,sqlite,sql-delete
I am creating a app in which i have used sqlite db.I have successfully created the table and inserted data into it.But when i try to delete the data , the query doesn't seems to be working. Code public void deleteSubject(int Id) { String query = "DELETE FROM " +...

Inner join on delete query is that bad?

c#,mysql,delete,inner-join,sql-delete
I'm currently trying to delete a row from a MySQL table using C#. I have two very basic tables: 'head_doc' and 'Doc_details'. Head_doc contains a field called id_doc. Doc_details has also a field called id_doc and various other fields. I'm trying to delete all of the details within Doc_details for...

Comparing consecutive rows using oracle

sql,oracle,loops,date,sql-delete
I have a table which looks something like this: | ID | FROM_DATE | TO_DATE | ------------------------------ | 1 | 1/1/2001 | 2/1/2001| | 1 | 2/1/2001 | 3/1/2001| | 1 | 2/1/2001 | 6/1/2001| | 1 | 3/1/2001 | 4/1/2001| | 2 | 1/1/2001 | 2/1/2001| | 2 |...

MySQL Deadlock - accessing different primary key values also creating deadlock

mysql,multithreading,sql-update,sql-delete
I went through MySQL logs and found the reason for deadlock 1st thread is trying to execute below query. Which is waiting for thread 2. UPDATE M_SAMP SET FLAG=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19)) The result of inner query (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID...

SQLite3: Delete all rows that share column value and have one or more empty values in another column

sqlite3,sql-delete
I have a sqlite3 table with historical stock prices for different companies. The first column is a date, the second is the ticker symbol for the company, and the third column is the stock price for that company at that date. Some stock prices are missing and I would like...

ORACLE SQL - Delete any records with count > 1

sql,oracle,sql-delete
I am collecting customers into a temp table who have a particular product, for this example will be called "Foobar". CREATE TABLE TEMP_CUSTOMERS AS SELECT ROW_ID, CUSTOMER_ID FROM PRODUCT prod WHERE prod.NAME = 'Foobar' Some customers may have multiple Foobars, so there count would be greater than 1 in the...

Delete data from table with foreign keys as primary

sql,postgresql,foreign-keys,sql-delete
I am programming a drawing surface with elements and connections between them. My two tables for the surface look like this: CREATE TABLE element ( elid character varying(60) NOT NULL, data json, surface_id character varying(60) NOT NULL, type character varying, CONSTRAINT element_primary_key PRIMARY KEY (elid), ) CREATE TABLE connection (...

sql delete based on group by

sql,postgresql,sql-delete
I have following query: select user_id, trajectory_id, count(trajectory_id) from point group by user_id, trajectory_id It returns following result: And I want to delete all records from point table where count in produced table is < 5. Right now I have following query, but it is not compiling. What is wrong...

PDO query is always returning 1 or true

php,sql,select,pdo,sql-delete
I am trying to check if a row exists before I delete it. The row in my table doesn't exist but it always returns 1: $orders = $this->db->prepare("SELECT * FROM orders WHERE id=? AND user=?"); $check = $orders->execute(array($message,$this->model->checkapi($data,$message))); echo $check; if($check){ $deleteorder = $this->db->prepare("DELETE FROM orders WHERE id=? AND user=?");...

SQL Delete from two tables

mysql,sql,sql-delete
I have two tables: 'player' and 'game'. The primary key of player is the foreign key in the table game. 'player' --> PK = idPlayer 'game' --> FK = player_idPlayer So now I want to delete one player in the players table. The SQL statement should also delete all entries...

What is wrong with my mysql delete statement?

php,mysql,sql,codeigniter,sql-delete
Can anyone tell me what's wrong with me sql statement? I want it to delete all the table rows that the time interval is more than 1 minute. My sql statement does not show any errors but it wont delete any of the rows. By the way I'm using the...

Mysql DELETE from table using information from another table

mysql,sql,sql-delete
So I have these two tables: The Sites table holds all types of websites to vote on, and Voted_sites table contains all of the sites that been voted on. Now, sites should get deleted from Voted_sites, when the current date is greater than date_voted + interval vote_wait_time_hours. So in my...

Removing duplicates in an SQL database on a condition

mysql,sql,sql-delete
I need to remove duplicates from a table that looks like this: id post_author post_title ----------------------------------------------------------------------------- 21319 1 Youngstown State University 20535 1 Yo San University of Traditional Chinese Medicine 30268 29 Yo San University of Traditional Chinese Medicine 29747 29 Yeshiva University 21964 1 Yale University 29247 29 Yale...

EF 6 optional FK , delete still produces error

entity-framework,foreign-key-relationship,sql-delete
So i have these two simple models public class Blog { public int BlogId { get; set; } private string _Name; [Column("sNameColumn")] public string Name { get { return _Name; } set { _Name = value; } } public virtual List<Post> Posts { get; set; } } public class Post...

Update/delete queries cannot be typed JPA

java,spring,jpa,entitymanager,sql-delete
I'm using the Spring framework for working on training projects. I'm also using JPA and have been having a hell of a time. In my DAO implementation class, I've written: @Override public void deleteEntries(Module mod) { System.out.println(mod.getDescription()); entityManager.createQuery("delete from TrainingEntry e where e.module = :mod and e.completedDate IS NULL", TrainingEntry.class).setParameter("mod",...

Writing the SQL query based on 2 tables

sql,sql-server,sql-delete
I am really confused writing this SQL query, it might be easy but I still cannot come to the right solution. Idea: Delete rows (foreign keys) from TableA based on TableB, if in TableB exist Primary Keys which match some other value within TableB. For table B it should look...

Understanding DELETE and DROP command

sql,database,oracle,sql-delete,sql-drop
I am a student of Computer Science. Recently I am trying to learn database. I found a link describing about the difference between DELETE and DROP command. I understand most of the difference between them with the most important one - DELETE removes only data where DROP removes the table/object...

mySQL - Can't delete table row with PHP (not showing an error)

php,mysql,database,sql-delete
I've spent today going through tons of similar questions and trying to figure out what is wrong with my code, lots of issues people had with back ticks, quotes, etc but none seem to help or change my cause. My code is no producing any errors, but when I use...

HibernateException: Illegal attempt to associate a collection with two open sessions

hibernate,session,orm,sql-delete
Here is my tables: Orders.java @Entity public class Orders implements Serializable { @Id @GeneratedValue private Integer id; @Column(nullable = false) private Date orderDate; @ManyToOne(cascade = CascadeType.ALL) private User user; @OneToMany(mappedBy = "orders", cascade = CascadeType.ALL) private Set<OrderItem> orderItems = new HashSet<OrderItem>(); //getter/setters I am going to delete one record from...

How to track the login details in SQL Server who deleted the data

sql-server-2012,database-administration,sql-delete
I came across a weird situation in SQL Server db. I see few developer are randomly deleting/updating the data over multiple tables. Is there any mechanism exits in SQL Server to capture all information like who deleted the records or who updated the records ext? Details: Microsoft SQL Server 2012...

How may I delete using Titanium with Alloy Framework?

javascript,android,titanium,titanium-alloy,sql-delete
I am trying to delete only one record using Alloy framework: I created in my controller .js this code to show the variables I got from a ListView: var args = arguments[0] || {}; $.titleLabel.text = args.titulo || 'Default Title'; $.authorLabel.text = args.author || 'Default Author'; And I have created...