Is there a way in SQL 2000, at the server, for a view to be prewritten and run at a specific time to delete rows in a table? I have the Delete query piece figured out. But I would like SQL to run this delete query daily to delete old data without user intervention. Is this possible?
Thanks
possible to send Pivot query results as automated database email ?
View 3 Replies View Relatedthis is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
View 2 Replies View RelatedI need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?
What I initially tried to do was:
Code:
DELETE FROM tblProductState
JOIN tblProduct
ON tblProduct.id_Product = tblProductState.id_Product
WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
How can I do this without using JOIN. Use a sub-query? How?
Thanks
Hi
I need some help on a query. I need to delete some records from a table, this table has a dependency to another table
Table 1: dbo.Accounts and Table 2: dbo.AccountsToUser
In the dbo.Accounts table there are
AccountId and OwnedByAccountId
54708002 54708001 54708003 54708001 65708002 65708001 65708003 65708001 54708001 2233440165708001 NULL
In the dbo.AccountsToUser there are
AccountId and UserId
65708002 10065708003 10165708003 10465708003 10654708001 19465708002 199
What I need is to delete every record from dbo.AccountsToUser that has an account connection to an account in the dbo.Accounts that has OwnedByAccount like NULL
So in the example above I should delete from dbo.AccountsToUser
65708002 10065708003 10165708003 10465708003 106
Since they are connected to 65708001 which has OwnedByAccountStatus like NULL
I could delete the records manually since the table is still hand able, but I need this to be a daily job so all help would be very nice
Thanks!
Can any one please correct this query.Shall i write like this.Please correct this query.
Delete globalDocs.dbo.gdoc_File set IsTrue=1 where FileID='abc'
In sql server ...is there any opton to delete any particular row..
i have table student
no name
1 raja
2 pravin
3 abraham
suppose i what to delete 2nd row is there any option to delete whithout specifying no or name just only row(ie 2)
pl reply me....
Hi friends, I want to delete more than record using id.i pass group of id(with string concat) like 10|11|20|25. in stored procedure i want to delete corresponding record.10112025 any idea?Thanks,Durai
View 3 Replies View RelatedBelow are 2 tables and data that I have.
Table1
col1col2col3
--------------------
11aaa
12bbb
13ccc
21ppp
22qqq
23rrr
Table2
col1col2
-------------
11
12
21
Now I want to delete all rows from Table1 which doesn't have a matching entry in Table 2. After the delete, this is what my Table1 data should be:
Table1
col1col2col3
--------------------
11aaa
12bbb
21ppp
Can some one give me a query to accomplish this? Thanks in advance for your help.
Hi folks
Hoping someone can help me out with my query query!
I'm trying to write a script to do the following: delete everything from table1 where column A and B (of table1) does not match column A and B of table2
Any ideas??
Thanks as ever,
Georgia
Hi,
Is this a valid SQL Server query :
DELETE FROM D FROM D WHERE LEFT JOIN H ON H.key=D:key WHERE H.key IS NULL
Please advise.
Thanks,
Sam
Hi,
Is this a valid SQL Server query :
DELETE FROM D FROM D WHERE LEFT JOIN H ON H.key=D.key WHERE H.key IS NULL
Please advise.
Thanks,
Sam
Hi,
Not sure if this is possible in a delete query that is why I'm posting out of curiosity.
I have a table with one column (int datatype)
WorkItem
1
1
2
1
3
1
----(6 rows)
Can I delete the first record whose workitemid is 1
So that after the delete the resulting dataset in
WorkItemID
1
2
1
3
1
----(5 rows)
Thanks,
yumyum113
hi all,
how do i perform this query?
DELETE FROM tblStkAdjDetail
WHERE (SELECT ItemStorageID FROM tblStkAdjDetail WHERE Status='NEW'
AND ItemStorageID NOT IN (SELECT ItemStorageId FROM tblTempTableForRecvPacking) )
~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
Hi All
I am beginner ot SQL Server.
I want to know that when we delete a row or a set of Rows from a table, it'll only make the space available for subsequent inserts into it or will the Delete also free the memory used by the table.
Suppose I am inserting customer records in the details table when the customer comes into the system. If i make a logic to delete the customer record from the table and insert into the backup table when it leaves the system(As the data inserted is quite large and my application queries into this table at each transaction). Will it help in optimizing the SQL Queries or it is useless to do so.
Thanks
Harsh Dhawan
Harsh Dhawan
I have part of a stored proc that I need help with.
I need to figure out how to delete infro from the
tblManifest using the @ssnDelete variable.
I Have multiple records ffor the given above variable.
Would this need to loop in order to work and that is the solution for this.
I get ther following error when I run the full stored proc.
------------------------------
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
------------------------------
Here is the code fro part of the proceedure.
Thanks in advance.
Gene
----------------------------------------------------------
CREATE TABLE dbo.tblTempSSN(
ssn varchar(11) null);
OPEN SYMMETRIC KEY SymKeySSN
DECRYPTION BY CERTIFICATE CertSSN;
INSERT INTO tblTempSSN(ssn)
SELECT CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) AS SSN
FROM tblSoldier s, tblManifest m
WHERE CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) = m.ssn
CLOSE SYMMETRIC KEY SymKeySSN;
declare @ssnDelete varchar(11);
set @ssnDelete = (select (m.ssn) as ssn
from tblManifest m, tblTempSSN t
where t.ssn = m.ssn);
delete
from tblManifest
where ssn = '@ssnDelete';
DROP TABLE dbo.tblTempSSN;
----------------------------------------------------------
I have part of a stored proc that I need help with.
I need to figure out how to delete infro from the
tblManifest using the @ssnDelete variable.
I Have multiple records ffor the given above variable.
Would this need to loop in order to work and that is the solution for this.
I get ther following error when I run the full stored proc.
------------------------------
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
------------------------------
Here is the code fro part of the proceedure.
Thanks in advance.
Gene
----------------------------------------------------------
CREATE TABLE dbo.tblTempSSN(
ssn varchar(11) null);
OPEN SYMMETRIC KEY SymKeySSN
DECRYPTION BY CERTIFICATE CertSSN;
INSERT INTO tblTempSSN(ssn)
SELECT CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) AS SSN
FROM tblSoldier s, tblManifest m
WHERE CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) = m.ssn
CLOSE SYMMETRIC KEY SymKeySSN;
declare @ssnDelete varchar(11);
set @ssnDelete = (select (m.ssn) as ssn
from tblManifest m, tblTempSSN t
where t.ssn = m.ssn);
delete
from tblManifest
where ssn = '@ssnDelete';
DROP TABLE dbo.tblTempSSN;
----------------------------------------------------------
Hi,
I'm really not very good at SQL sadly, so would really appreciate any help.
I'm basically working on a website that has a chatroom. I want it so the chatroom table only holds, say 15 lines of chat, then once a new line of chat is entered the last line in the table is deleted, so the table always has a maximum of just 15 rows.
The fields are:
MessageID
Poster
Message
DateTime
I've tried my best, but just don't know how to do it.
I suppose it's something like:
SELECT TOP 15 * FROM Chatroom ORDER BY MessageID DESC - this gives me the last 15 rows.
Then I need a delete statement to delete the rest?! Sorry, I am very bad at SQL, so any help would be great. This is written in a stored procedure.
Can the stored procedure pick up whether there are 15 or more rows in the table, and if so then delete all bar the newest 15 rows of chat?
To summarise: I want a stored procedure that checks if 15 or more rows exist, if they do then delete all bar the newest 15 rows.
Thanks,
Ricky
We're trying to construct a query that deletes records containing 48 particular phone numbers from a large db. The 48 numbers are the entire contents of a smaller db, in a field of the same name as in the larger db (home_phone). We're using Sequel Server 7.0 and Access 97. The db's are in Access now. We failed in Acess and now would like to import into sql and try it there.
Thanks,
Tad McArdle
Hallo,
Can anybody let me know what I need to change to make this query work:
delete
from a
inner b
on a.item = b.item
I get the error message "Incorrect syntax near the keyword 'inner'."
Thanks,
Stephen.
I need to delete records from one table base on criteria from another table. The example below from the Northwind database shows exactly what I want to do. I want to delete the records from the employee table who a terrorityID of 30346 in the EmployeeTerritories table.
Can someone tell me how to write a delete statement that will delete the rows returned from the following SQL Statement? The sql statement will return one employee name. I would lke to delete that one employee from the employee table and I haven’t been able to figure out how to do it.
////////// Sql Statment
SELECT dbo.EmployeeTerritories.EmployeeID, dbo.EmployeeTerritories.TerritoryID
FROM dbo.EmployeeTerritories INNER JOIN
dbo.Employees ON dbo.EmployeeTerritories.EmployeeID = dbo.Employees.EmployeeID
WHERE (dbo.EmployeeTerritories.TerritoryID = N'30346')
////////// end of sql statement
Thanks
GEM
I have 2 tables that are joined together by a primary key (Order Number). Can I use one SQL query to delete from both of the tables. One table contains the order information from a client (Order Number, Customer Name etc). The other table has order information (Order Number, Item Number, Quantity Ordered etc.)
I need one statement that will allow me to remove the items from both tables. Can this be done.
Thanks in Advance
Wes
What is the problem in :
DELETE FROM tblPickingTask
LEFT OUTER JOIN tblPickingSlip ON tblPickingTask.SPSID = tblPickingSlip.SPSID
WHERE tblPickingSlip.SPSID IS NULL
I whan to delete all record in tblPickingTask was not in tblPickingSlip
I have two tables PROFILES & ROLE
CREATE TABLE PROFILES(
ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
)
[Code] ...
This query joins both the tables and gets displayed in a grid.
select P.ID, Name, Address, Role, Applications
from PROFILES P
Inner Join ROLE R
ON P.ID= R.ID
I need to write a delete query which deletes data from both the table in a single query.
Hello,
I have a db with 4 tables are linked by different fields. I'll setup a general layout for you in hopes of getting some assistance with write a delete query based on a date range. T is table, f is field.
T1.F1 = T3.F1
T1.F2 = T2.F2
T1.F3 = T4.F3
T1.F4 = T2.F4 {DATE}
so as you can see, this will be an embedded delete of some type but I have to somehow cache the deleted values so that these values can be used for the parent delete (I think).
I'm new to this stuff so...let me know if my approach is wrong. Perhaps it was a poorly written db. I have to purge about 5GB worth of data.
Thanks in advance,
Sid.
I need to clear my query history. I wrongly select top 1000 rows from menu and this 1000 rows listed in 01.46 minutes. But,there are 11 million rows in the table and ı need to get the elapsed time.when I use the select * from "table name" , this 1000 rows automatically listed in 0 times.Now I need to clear all the history to get elapsed time correctly.
View 4 Replies View RelatedHi everyone,i'm new to ado.net and need some help. My problem:got one DB with real bad tablestructureand one new DB (structured!)i need do do an automated import from the old to the new DB, with check if the data row is new or updated.I try to do it this way-> 2 datasets merged together ->Problem: new entries in destination dataset have row state unchangedother way i tryed add rows from source to destination with add funktion -> problem no check if data exist and maybe is changedSo how can i get the merge working or how can i implemt an effective funktion to solve this problem. Thanks in advance!
View 5 Replies View RelatedHi, looking for some paths to take on this one. I have to backup the database to a LS120 floppy
drive. It has to be automated so the user can just click on a desktop icon and away it goes. Probably
an easy script buy i sure would appreciete some help on this one.
Thanks
Troy
Hi. I am using SQL 7.0.
Here is the situation... there are random (.csv) files being dropped into a specific directory on a daily basis. Is it possible to have SQL server automatically pick up this file and run it through the Import/Export data option so that it creates a SQL table from the original data? I need this whole sequence to be automated. Is this possible? Somebody please help... I will appreciate it greatly. Thanks a lot!
Alan
I am trying to automate the installation process of our Help
Desk software `Expert Advisor`. It uses a MS SQL Server 6.5
database. I can figure out the automation of Expert Advisor,
but is it possible to automate the installation of the
SQL drivers for each workstation? I don`t want or need any of the
utilities such as ISQL/w installed along with the driver--just the
SQL driver itself. Is there a runtime version of the database I should
use? I`m fairly new to SQL so please forgive me if I seem naive.
Any suggestions? Any help would be appreciated.
Toni
I don't know what happened on that first thread, but here it is again.
Thanks for your help.
Hello,
I have set up a .bat file to automatically export all of my server logs to a directory on my SQL server. From there I am trying to import those files into an event_log table that I have set up. (from there I am going to use Cold Fusion to parse the table each day and e-mail me with anything I should look at) The estimated ROI for my company is roughly $14.5k, it's easy so if you don't already have an error log monitoring solution, check this out!
Anyway I tried to use BCP in my .bat file but it keeps coming back with an unexpected end of file error.
Can I get some help with either:
1. Find a command line tool to import these .txt files with
-or-
2. Help me with the BCP issue?
Thanks in advance.
I am an intern at a university and we are looking for a way to automatically document tables, queries, etc. ColdFusion is the platform used, and SQL Server 2000. Is there a way to have the documentation performed that will capture tables, constraints, etc.? I have found some, but so far, none are working. Please help if you can!!! :confused:
View 4 Replies View Related