Delete the 'First' Record from a Table in SQL Server, Without a Where Condition

Delete the 'first' record from a table in SQL Server, without a WHERE condition

WITH  q AS
        (
        SELECT TOP 1 *
        FROM    mytable
        /* You may want to add ORDER BY here */
        )
DELETE
FROM    q

Note that

DELETE TOP (1)
FROM   mytable

will also work, but, as stated in the documentation:

The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

Therefore, it's better to use WITH and an ORDER BY clause, which will let you specify more exactly which row you consider to be the first.

SQL Select statment with remove first row

try this query

 SELECT * FROM (
    select ROW_NUMBER()OVER(ORDER BY id ASC) row,* from pdetails where pcode='P000437'
    )t
    WHERE row != 1;

How to delete first few records from a table without any criteria in PostgreSQL?

Try to prepare subquery with LIMIT as below

DELETE FROM txn_log
WHERE txn_log_pgm_id IN (SELECT txn_log_pgm_id
                         FROM txn_log
                         ORDER BY txn_log_timestamp asc
                         LIMIT 500)

DELETE whole table except for one row

The quickest way in most databases would be:

1. Select the record with id 1800 into a temporary table
2. Drop the original table
3. Copy the data from the temp table into the full table

Admittedly, this may not be possible due to triggers, constraints, and permissions. In many databases you can do something similar by modifying (2) to truncate the table instead of dropping it.

As for your original question, the overhead with actually deleting the rows and the data associated with them is going to dominate the query. How you do the comparison is irrelevant.

Sample code

create temp table saved as
    select * from t where id = 1800

truncate table t

insert into t
    select * from saved

I'm not sure about Postgres naming conventions for temporary tables, but this is the idea.

How do I delete the first matching record in Table B for each record in Table A?

You need some way to distinguish the rows to delete from the rows to keep. I've used someOtherColumn in the below to achieve this:

create table #DraftInvoiceRecords (
    employee int not null,
    amount int not null,
    units int not null,
    someOtherColumn int not null
)
create table #ReversedRecords (
    employee int not null,
    amount int not null,
    units int not null
)
insert into #DraftInvoiceRecords (employee,amount,units,someOtherColumn)
select 1,1,1,1 union all
select 1,1,1,2
insert into #ReversedRecords (employee,amount,units)
select 1,1,1
delete from dir
from
    #DraftInvoiceRecords dir
        inner join
    #ReversedRecords rr
        on
            dir.employee = rr.employee and
            dir.amount = rr.amount and
            dir.units = rr.units
        left join
    #DraftInvoiceRecords dir_anti
        on
            dir.employee = dir_anti.employee and
            dir.amount = dir_anti.amount and
            dir.units = dir_anti.units and
            dir.someOtherColumn > dir_anti.someOtherColumn --It's this condition here that allows us to distinguish the rows
where
    dir_anti.employee is null

select * from #DraftInvoiceRecords

drop table #DraftInvoiceRecords
drop table #ReversedRecords

How to delete only one row if several found?

Not the best way but, you could do this:

DELETE FROM tshirt
WHERE id IN (
   SELECT id FROM
   tshirt WHERE sku='%s' LIMIT 1
)

Delete first X lines of a database

Use LIMIT on your delete:

DELETE FROM table WHERE condition LIMIT 10

Or, if you don't want the condition

DELETE FROM table LIMIT 10

Remember that the order in which rows will be deleted is undefined - it depends on your DBMS configuration and table indices. You should include an ORDER BY so that the deletion is done in a defined order, e.g. ORDER BY id ASC to delete the lowest IDs first.

See the MySQL documentation for DELETE for more details.

Leave only first 50 records in SQL database and delete the rest

please try this

delete from scores_tbl Where
id not in
(select * from
(select id from scores_tbl order by score desc limit 50)
 as temp)

---

Related Topics