Delete all records from a table

databasesubsonic

How can I delete all records from a table using SubSonic?
The Delete method has three overloads, but each one expects some parameters.
And how can I delete records using a query (e.g. delete all records where column1 > 100)

Best Answer

The following will delete all rows from the TempTable which have an Id of greater than 56:

new Delete().From(TempTable.Schema)
  .Where(TempTable.Columns.Id).IsGreaterThan(56)
  .Execute();

Related Solutions

Mysql – How to select the nth row in a SQL database table

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

Sql – Insert into … values ( SELECT … FROM … )

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

Oracle
MS SQL Server
MySQL
Postgres
SQLite v3
Teradata
DB2
Sybase
Vertica
HSQLDB
H2
AWS RedShift
SAP HANA
Google Spanner

Best Answer

Related Solutions

Mysql – How to select the nth row in a SQL database table

Sql – Insert into … values ( SELECT … FROM … )

Related Topic