|
Hi all. I'm wondering if there is a means in SQL to delete a certain number of rows from a table, when ordered a certain way. In other words, I want to be able to say something like this:
delete the 10 oldest rows where <some condition> applies
...any thoughts?
I'm looking for something in SQL; I can do it fine with ADO.NET but ADO.NET is too slow and need something faster.
|
|
|
|
|
logan1337 wrote: Hi all. I'm wondering if there is a means in SQL to delete a certain number of rows from a table, when ordered a certain way. In other words, I want to be able to say something like this:
delete the 10 oldest rows where <some condition=""> applies
How do you define which are the oldest rows? Once you have the answer to that question you can add an ORDER BY clause and then a TOP 10
|
|
|
|
|
That is easy, just order by a certain date column, in ascending.
I've done a bit of reasearching this TOP command, but from what I understand, you in fact cannot use ORDER BY with TOP when deleting. According to the documentation on MSDN, TOP simply selects n random rows at a time, and is really only intended for splitting up delete operations over several transactions.
Can anyone verify this? It would be nice to be able to delete n rows off the top of a sorted table.
Logan
|
|
|
|
|
You can do this:
DELETE<br />
FROM MyTable<br />
WHERE ID IN (SELECT TOP 10 ID FROM MyTable ORDER BY HistoricalDate)
If you have any other conditions with which to filter the data to be deleted then they should be put in the subquery.
|
|
|
|
|
Ah yes! Of course, thanks! I was so caught up on reading that it couldn't be used with deletes that I didn't realize you could delete using a select! 
|
|
|
|
|
Hello,
VS 2005
Using untyped dataset.
I have a xml file which I am using for local storage.
I have a dialog box that displays text boxes for uses to add another new incident.
I read in the xml file and add the following code to set the auto increment properties of the dataset table.
However, everytime the user wants to add a new incident the auto-increment properties will be set again and again. So the new row will not be incremented and will always have the same incidentID PK value.
Is there a way to only set these only once, so that when the add incident form loads it won't re-set the auto-increment properties. I was thinking of having some sort of global untyped dataset.
My code is below:
many thanks for any advice on this.
<br />
'Read the xsd file<br />
dsIncidents.ReadXmlSchema(incidentXSDpath)<br />
'Read in the xml fil<br />
dsIncidents.ReadXml(incidentXMLpath, XmlReadMode.DiffGram)<br />
<br />
Dim dt As DataTable = dsIncidents.Tables(0)<br />
'Set the auto increment properties<br />
dt.Columns("IncidentID").AutoIncrement = True<br />
dt.Columns("IncidentID").AutoIncrementSeed = 0<br />
dt.Columns("IncidentID").AutoIncrementStep = -1<br />
<br />
Dim dr As DataRow = dt.NewRow()<br />
dr("Company") = Me.cboCustomer.Text<br />
dr("Contact") = Me.cboContact.Text<br />
dr("PhoneNo") = Me.txtPhone.Text<br />
dt.Rows.Add(dr)<br />
<br />
'Write to the xml and schema files<br />
dsIncidents.WriteXmlSchema(incidentXSDpath)<br />
dsIncidents.WriteXml(incidentXMLpath, XmlWriteMode.DiffGram)<br />
|
|
|
|
|
i write PROCEDURE to update record in datadate this PROCEDURE have where statment ,,,
mow how can if the PROCEDURE make update successfully to database or not ???
Palestine
|
|
|
|
|
now how can know if the PROCEDURE make update successfully to database or not ???
Palestine
|
|
|
|
|
i want a transact sql for select queries which returns
all rows that include something like here:
table1
code, name, address, ?? , ??
1 , john , toronto , ..
2 , mike , new york , ..
3 , alec , john city, ..
...
select * from table1 where includes 'john'
result is:
1 , john , toronto
3 , alec , john city
[note1: i do not know field names]
[note2: fields are in different data types]
[ like : int, numeric, float, char, text, image ....]
[note3: full text indexing is appropriate for string fields,not for all types]
|
|
|
|
|
amraouf wrote: [note1: i do not know field names]
Then you will need to find out.
amraouf wrote: [note2: fields are in different data types]
It is a bit difficult to do a string match on a non-string column.
amraouf wrote: [note3: full text indexing is appropriate for string fields,not for all types]
Then why not create a string column with all the searchable stuff in it (including string representations of non-string column) and index that. If you make the column name consistent across all tables then you don't need to know each individual column name, because all tables have it.
|
|
|
|
|
ooops!
its a bit difficult to change the structure of a database for such a query !
|
|
|
|
|
amraouf wrote: its a bit difficult to change the structure of a database
How come?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
amraouf wrote: its a bit difficult to change the structure of a database for such a query !
No it isn't. This is a simple change.
1. Add the extra column on each table that requires it.
2. Add triggers to each table to update the new column each time there is an insert or update
If you have difficulty figuring out how to change the database structure from one thing to another then you might want to buy a book on refactoring databases. There is a very good one published by Addison Wesley.
|
|
|
|
|
Colin Angus Mackay wrote: This is a simple change.
1. Add the extra column on each table that requires it.
2. Add triggers to each table to update the new column each time there is an insert or update
Thank you, I was too lazy to go to that extent earlier 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Thank you very much
Of course I designed the database and I am the first one who can change or update it
but you know
in this phase of project i can not push my time on this matter
|
|
|
|
|
amraouf wrote: Of course I designed the database and I am the first one who can change or update it
but you know
in this phase of project i can not push my time on this matter
When I put a project together I figure out what are the bigger risks and do them up front so I've got as much time as possible to make changes if my ideas don't work out. It sounds to me that you need to be identifying those risks before it gets too late.
If the feature is not a "Must have" then it can be dropped. If the feature is required then the solution I gave is probably the quickest route to it being implemented.
|
|
|
|
|
select * from TableName where name + address like '%john%'
I Love SQL
|
|
|
|
|
And this acounts for the numeric types how exactly? Also, it is hardly an optimised solution - The database is forced to scan the entire table each time.
|
|
|
|
|
I want a create a constraint such that if value of cola='Y'
then value of colb cannot be null.Please help
|
|
|
|
|
to my knowledge this can be done only using trigger.
Regards
KP
|
|
|
|
|
i have a table called loginfo in the server which is sql server is windows authenticated . i have user account in the server and if i have to connect to the server(loginfo) via an application do i have to give my uerid and password to the connection string ? how far is this secure..
|
|
|
|
|
Hi all,
I want join in 5tables in my sqlserver database.
How I join 5tables,empno is a primary key for all tables.
thanks
|
|
|
|
|
select *
from
tab1 inner join
tab2 on tab1.empno=tab2.empno inner join
tab3 on tab2.empno=tab3.empno inner join
tab4 on tab3.empno=tab4.empno inner join
tab5 on tab4.empno=tab5.empno
where
1=1
|
|
|
|
|
hi amraouf
Thanks for your reply,
|
|
|
|
|
hey i am trying to make a select query on a dataset
with the .select() method which equals
to the following lines writen in sql:
select *
from CA_ORDERS
where SUPPLIER in
(
select sup_name
from SUPPLIER
where SUP_RELATION = 'Drop shipping' )
well i may say i dont know the specific format of the .select() method
on datasets , cause it seems to be diferent from SQL lang.
how can i implement the 'IN' Filter?
Net
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
