How to delete a few rows via SQL

Not specified by the original author.

TL;DR

In QuestDb Update and Delete statement are not supported. The ways to delete data are: - Drop a partition - Write a copy of the table without the rows you want to delete, drop table and then rename the table to the one you wanted.

Steps

1. In QuestDb Update and Delete statement are not supported. At least now. The ways to delete data are: - Drop a partition - Write a copy of the table without the rows you want to delete, drop table and then rename the table to the one you wanted. Something like Create table mytablecopy AS ( SELECT * FROM mytable where columnvalue != 2 ) Timstamp(...) PARTITION BY ...; DROP TABLE mytable; RENAME table mytablecopy TO mytable; These are costly workarounds for exceptional cases.

When to use

You are seeing this: I can't find any statement allowing me that. Use this skill when you run into "How to delete a few rows via SQL?".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Source: Stack Overflow #65976247 (accepted answer, 10 votes): is there a way to delete a view rows matching a query in QuestDB? I can't find any statement allowing me that. This would be the best option: delete from mytable where columnvalue==2; Thanks!

Verified resolution: In QuestDb Update and Delete statement are not supported. At least now. The ways to delete data are: - Drop a partition - Write a copy of the table without the rows you want to delete, drop table and then rename the table to the one you wanted. Something like Create table mytablecopy AS ( SELECT * FROM mytable where columnvalue != 2 ) Timstamp(...) PARTITION BY ...; DROP TABLE mytable; RENAME table mytablecopy TO mytable; These are costly workarounds for exceptional cases.

Source URL: https://stackoverflow.com/questions/65976247/how-to-delete-a-few-rows-via-sql

Not specified by the original author.

TL;DR

Group by the key columns and keep groups with HAVING COUNT(*) > 1 to find duplicate keys; use a COUNT(*) OVER (PARTITION BY ...) window to see the full duplicated rows. The GROUP BY version tells you which keys repeat, the window version shows you every row involved so you can decide what to keep.

how to write a query that finds duplicates

Use this when

• You need a list of keys that appear more than once
• A uniqueness assumption broke and you want the evidence
• You want to inspect full duplicate rows before deleting any

Not for this skill when

• Duplicates appear only after a join (check join-key uniqueness instead)
• You are deduplicating in pandas (duplicated() covers that)
• You need ongoing dedupe of an event stream (thats a pipeline design problem)

Steps

1. Find which key values repeat. This is the fastest diagnostic:

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n DESC;

Expected output: one row per duplicated email with its count, most-duplicated first. Zero rows means the key is unique and your problem is elsewhere.

2. Check multi-column keys the same way, grouping by the full tuple:

SELECT customer_id, order_date, COUNT(*) AS n
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;

Expected output: the duplicated combinations. Uniqueness lives at the combination level, so group by every column in the key.

3. See the full rows, not just the keys, with a window function:

SELECT *
FROM (SELECT u.*, COUNT(*) OVER (PARTITION BY email) AS dup_count
      FROM users u) s
WHERE dup_count > 1
ORDER BY email, created_at;

Expected output: every row that shares an email with at least one other, with the group size attached. Ordering by a timestamp inside each group shows you which row came first.

4. Compare the rows within each group to decide what counts as a duplicate:

SELECT email,
       COUNT(*) AS n,
       COUNT(DISTINCT name) AS distinct_names,
       MIN(created_at) AS first_seen,
       MAX(created_at) AS last_seen
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Expected output: per duplicated key, whether the other columns agree. Same name and close timestamps means a true double-insert; different names means the key isnt really an identifier.

5. Once you know what to keep, delete the extras with ROW_NUMBER, keeping the earliest:

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
  ) s WHERE rn > 1
);

Expected output: a delete count equal to total rows minus distinct keys. Run steps 1-3 first on a copy if this is production data.

Variant phrasings

SQL find duplicate rows in table

Step 1 for keys, step 3 for full rows. Pick based on whether you need the list or the evidence.

how to check for duplicates in SQL Server / MySQL / Postgres

The GROUP BY plus HAVING pattern is standard SQL and works everywhere. The window-function variant needs a modern engine version, which anything from the last decade has.

SQL query to find duplicate values in one column

That is step 1 exactly. One column, GROUP BY it, HAVING COUNT(*) > 1.

Why it happens

Duplicate rows accumulate because inserts rarely enforce the uniqueness the analyst assumes: missing unique constraints, upserts that insert instead of updating, retries that replay a write, or two systems writing the same entity. The queries above dont prevent duplicates, they make the invisible visible so you can add the constraint or fix the writer.

Edge cases

• NULL keys: GROUP BY treats all NULLs as one group, so nullable key columns can report massive phantom duplication. Filter or coalesce NULLs first.
• Case and whitespace: 'Ann' vs 'ann ' group separately. Normalize with LOWER and TRIM in both the check and the eventual constraint.
• Near-duplicates (typos, 'Jon' vs 'John') wont be caught by exact grouping; that needs fuzzy matching, a different problem.
• Very large tables: the window-function version scans everything. Run the GROUP BY version first to see if the problem is even real before the expensive query.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstQHWw7ePQu96EbtA-33ygg

Resolved from the public thread: https://vectle.com/posts/pstQHWw7ePQu96EbtA-33ygg

Thread query: how to write a query that finds duplicates

TL;DR: Group by the key columns and keep groups with HAVING COUNT(*) > 1 to find duplicate keys; use a COUNT(*) OVER (PARTITION BY ...) window to see the full duplicated rows. The GROUP BY version tells you which keys repeat, the window version shows you every row involved so you can decide what to keep.

Not specified by the original author.

TL;DR

Jira Link: DB-7435 I noticed DELETE statement in YugabyteDB can't delete records when specifying a float key via JDBC. Never test floats for exact equality: use the numeric type or an almost-equal comparison instead.

Steps

1. Jira Link: DB-7435 I noticed DELETE statement in YugabyteDB can't delete records when specifying a float key via JDBC. I think you can reproduce this issue using the following code.
2. [andrei-mart (contributor)]: Float keys fail exact-equality lookups in YugabyteDB due to internal float rounding. Never test floats for exact equality: use the numeric type or an almost-equal comparison instead. PostgreSQL and MySQL docs carry the same warning.

When to use

You are seeing this: I think you can reproduce this issue using the following code. Use this skill when you run into "DELETE doesn't work when specifying a float key via JDBC".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Sources

• https://yugabyte.atlassian.net/browse/DB-7435

In gh:yugabyte/yugabyte-db#18461 (https://github.com/yugabyte/yugabyte-db/issues/18461), the reporter ran into this: Jira Link: DB-7435 I noticed DELETE statement in YugabyteDB can't delete records when specifying a float key via JDBC. I think you can reproduce this issue using the following code. The verified resolution: [andrei-mart (contributor)]: Float keys fail exact-equality lookups in YugabyteDB due to internal float rounding. Never test floats for exact equality: use the numeric type or an almost-equal comparison instead. PostgreSQL and MySQL docs carry the same warning.