¿Cómo hago para borrar los últimos o primeros registros en una tabla de una base de datos con SQL?
Me gustaría saber, como hago para borrar los n ultimos o primeros registros en una tabla.
Estoy trabajando con una BD interbase
Estoy trabajando con una BD interbase
1 respuesta
Respuesta de athrarn
1
Me pones en un problema, se hacerlo en Oracle y en de SQL Server. Te mando ejemplo de SQL server y de Oracle :
Para access y SQL Server :
Para mostrar los N primeros registros vale con Usar la sentencia TOP. Esta funciona asi :
SELECT TOP [Numero de registros] ...
La documentacion del TOP la tienes aqui :
http://users.starpower.net/rjhalljr/Serve/AccSql/sql.html
Por si no la ves es esta...
Returning Groups Of Rows In Sequence
For web pages, you often want to return rows in groups of some number. For example, you may want to insert the first twenty five rows into the first page, the next twenty five into the second page, and so on. If you can't or don't want to do this in the client applications, the following SQL statements will do the job. This approach assumes that the rows in the table are ordered on one column (fldOrder).
SELECT TOP 25 * FROM tbl;
Now it gets a little tricky. To get the next group of twenty five rows, start by selecting the first fifty.
SELECT TOP 50 * FROM tbl;
Now use the query above as a subquery in a query that pulls the bottom twenty five from the selected fifty.
SELECT TOP 25 *
FROM (
SELECT TOP 50 * FROM tbl
)
ORDER BY fldOrder DESC;
That gets the correct twenty five rows, but in the wrong order. The last step is to put them back in the proper order.
SELECT *
FROM (
SELECT TOP 25 *
FROM (
SELECT TOP 50 * FROM tbl
)
ORDER BY fldOrder DESC
)
ORDER BY fldOrder;
The SQL statement above will run, but when you close the query and then reopen it in SQL View, you'll see that Access has changed the statement. Don't worry about it. Either version works.
Para Oracle, la sentencia es con un where :
select * from TABLA where rownum<100
RowNum representa el número de registro del campo de salida. Es decir, no puedes decir que rownum>100 dado que cada vez que intentase mostrar un registro le intentaria dar el número 1 y si no sale, al siguiente le daria el número 1... Y no te sacaria ningun registro.
Ah, recuerda que los N últimos registros de una consulta son los N primeros en orden inverso, y que borras en SQL solo poniendo delete donde estaba el select y los campos, y te borrará lo que escribiste en la select.
Para access y SQL Server :
Para mostrar los N primeros registros vale con Usar la sentencia TOP. Esta funciona asi :
SELECT TOP [Numero de registros] ...
La documentacion del TOP la tienes aqui :
http://users.starpower.net/rjhalljr/Serve/AccSql/sql.html
Por si no la ves es esta...
Returning Groups Of Rows In Sequence
For web pages, you often want to return rows in groups of some number. For example, you may want to insert the first twenty five rows into the first page, the next twenty five into the second page, and so on. If you can't or don't want to do this in the client applications, the following SQL statements will do the job. This approach assumes that the rows in the table are ordered on one column (fldOrder).
SELECT TOP 25 * FROM tbl;
Now it gets a little tricky. To get the next group of twenty five rows, start by selecting the first fifty.
SELECT TOP 50 * FROM tbl;
Now use the query above as a subquery in a query that pulls the bottom twenty five from the selected fifty.
SELECT TOP 25 *
FROM (
SELECT TOP 50 * FROM tbl
)
ORDER BY fldOrder DESC;
That gets the correct twenty five rows, but in the wrong order. The last step is to put them back in the proper order.
SELECT *
FROM (
SELECT TOP 25 *
FROM (
SELECT TOP 50 * FROM tbl
)
ORDER BY fldOrder DESC
)
ORDER BY fldOrder;
The SQL statement above will run, but when you close the query and then reopen it in SQL View, you'll see that Access has changed the statement. Don't worry about it. Either version works.
Para Oracle, la sentencia es con un where :
select * from TABLA where rownum<100
RowNum representa el número de registro del campo de salida. Es decir, no puedes decir que rownum>100 dado que cada vez que intentase mostrar un registro le intentaria dar el número 1 y si no sale, al siguiente le daria el número 1... Y no te sacaria ningun registro.
Ah, recuerda que los N últimos registros de una consulta son los N primeros en orden inverso, y que borras en SQL solo poniendo delete donde estaba el select y los campos, y te borrará lo que escribiste en la select.
