Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
btamulis
Yak Posting Veteran
64 Posts |
 Posted - 2012-06-01 : 13:00:24
|
| I have to delete 401 records from a data table. The 401 records have a common primary key and 401 sequential unique fields.Example:SOPNUMBE DEX_ROW_IDXXX1234 001XXX1234 002XXX1234 003XXX1234 004thruXXX1234 401My delete statement would be:delete sop10200 where sopnumbe = 'XXX1234' and dex_row_id in (001,002,003 etc)my question is - is there any sql syntax that allow something like:delete sop10200 where sopnumbe = 'XXX1234' and dex_row_id in (001 thru 401) or do I have to specify the 401 records? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-06-02 : 10:37:50
|
| I don't see this as a normalization issue - not sure why you would think it is.If the dex_row_id is contiguous you can use greater than and less than. If you have gaps that need to be kept, you can use ranges for those gaps, or identify the gaps to be kept.There are a lot of options, depending on what the values are that need to be kept in the table after the delete.DELETE FROM sop10200 WHERE sopnumbe = 'XXX1234' AND dex_row_id BETWEEN '001' AND '401'; -- if contiguous valuesIf certain values need to be kept, write a SELECT that identifies the rows to be kept, then you can use a NOT EXISTS or a join to delete the rows that don't exist in the table. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mani_12345
Starting Member
35 Posts |
Posted - 2012-06-04 : 00:38:09
|
| i hope below query wll solve your probmdelete tab4_name where sopnumber = 1111234 and dex_row_id In (select top (10) dex_row_id from tab4_name) |
|
|
|
|
|
|
delete statement