General discussion

Locked

removing data from SQL table

By thunderballs ·
Would like to know how to clear or archive data from SQL table data?. Data is 7.5 GB at the moment.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

removing data from SQL table

by Joseph Moore In reply to removing data from SQL ta ...

Well, I don't know what SQL engine you are running (Oracle, Sybase, MS SQL), so I am gonna post the Delete syntax for Microsoft SQL Server:

DELETE
[ FROM ]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}

[ FROM { < table_source > } [ ,...n ] ]

[ WHERE
{ < search_condition >
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
|cursor_variable_name
}
] }
}
]
[ OPTION ( < query_hint > [ ,...n ] ) ]

Collapse -

removing data from SQL table

by Joseph Moore In reply to removing data from SQL ta ...

< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ]
| rowset_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >

< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| < joined_table >

< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
[ < join_hint > ]
JOIN

< table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK | SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}

< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}

< query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}

Collapse -

removing data from SQL table

by Joseph Moore In reply to removing data from SQL ta ...

Examples
A. Use DELETE with no parameters
This example deletes all rows from the authors table.

USE pubs
DELETE authors

B. Use DELETE on a set of rows
Because au_lname may not be unique, this example deletes all rows in which au_lname is McBadden.

USE pubs
DELETE FROM authors
WHERE au_lname = 'McBadden'

C. Use DELETE on the current row of a cursor
This example shows a delete made against a cursor named complex_join_cursor. It affects only the single row currently fetched fromthe cursor.

USE pubs
DELETE FROM authors
WHERE CURRENT OF complex_join_cursor

D. Use DELETE based on a subquery or use the Transact-SQL extension
This example shows the Transact-SQL extension used to delete records from a base table that isbased on a join or correlated subquery. The first DELETE shows the SQL-92-compatible subquery solution, and the second DELETE shows the Transact-SQL extension. Both queries remove rows from the titleauthors table based on the titles stored in the titles table.

/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')

/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'

E. Use DELETE and a SELECT with the TOP Clause
Because a SELECT statement can be specified in a DELETE statement, the TOP clause can alsobe used within the SELECT statement. For example, this example deletes the top 10 authors from the authors table.

DELETE authors
FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Software Forums