Delete Query

Mar 31, 2008

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'

View 6 Replies


ADVERTISEMENT

I Use SQL 2000, Can You Use One Delete Query To Delete 2 Tables?

Nov 26, 2007

this 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

View 1 Replies View Related

How To Run Delete Query / Delete Several Rows Just By One Click ?

Feb 16, 2008

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 Related

DELETE Query - Can't JOIN. Need Sub-query?..

Mar 30, 2007

I 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

View 4 Replies View Related

Delete Query

Feb 28, 2008

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!
 

View 6 Replies View Related

Is There Any Sql Query To Delete Any Particular Row

Apr 11, 2008

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....

View 6 Replies View Related

Delete Query?

Apr 12, 2008

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 Related

Delete Query

Feb 25, 2002

Below 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.

View 1 Replies View Related

Delete Query Help

Oct 7, 2006

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

View 1 Replies View Related

Delete Query ?

Aug 2, 2004

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

View 1 Replies View Related

Delete Query ?

Aug 2, 2004

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

View 2 Replies View Related

Help With Delete Query

Apr 5, 2007

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

View 8 Replies View Related

Delete Query

May 25, 2007

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)/¯ ~~~

View 10 Replies View Related

Query Regarding Delete

Feb 21, 2008

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

View 4 Replies View Related

Delete Query

Aug 7, 2007

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;
----------------------------------------------------------

View 2 Replies View Related

Delete Query

Aug 7, 2007

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;
----------------------------------------------------------

View 4 Replies View Related

SQL Query - Delete All Rows Bar The Last 10? Help Please!

Jan 6, 2008

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

View 5 Replies View Related

Unusual Delete Query

Oct 3, 2000

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

View 1 Replies View Related

Delete Query With Inner Join

Nov 21, 2005

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.

View 3 Replies View Related

Delete Query Using Join

Jun 5, 2007

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

View 3 Replies View Related

Delete From 2 Tables With 1 SQL Query

Mar 23, 2004

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

View 6 Replies View Related

Error In Delete Query

Apr 7, 2004

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

View 3 Replies View Related

Delete Query For Two Tables

Nov 26, 2013

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.

View 3 Replies View Related

Complex Delete Query Help.

Jul 27, 2007

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.

View 2 Replies View Related

How To Delete Query History

Aug 27, 2015

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 Related

SQL Automated Delete Query?

May 7, 2008

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

View 1 Replies View Related

Query To Delete Millions Of Records

Jul 23, 2012

i have a query to delete millions of records. I whant to delete in batches of a 1000. My Select join statement will return millions of records so this takes alot of time how to i select a 1000 records delete everything that his not in those record and loop and not select the same records again.Here is what i have :

DECLARE @i INT
WHILE (1=1)
BEGIN
BEGIN TRAN
DELETE TOP(1000) FROM dbo.ABC123
WHERE SUBSTRING(dumbdumb,1,8) NOT IN

[code]....

View 3 Replies View Related

How To Delete A File Through Query Analyser

Mar 1, 2007

I am trying to restore a DB. After Detaching the DB I wanna delete the Ldf . How to delete this physical file Using Sql Query????

Thank You in advance.

View 3 Replies View Related

Delete Query In Access W/SQL Back-End

Nov 15, 2007

I Have read a few of the postings and I hate to beat a dead horse. But I am desperatly looking for your Help...

I am converting all of my Access Databases over to SQL server 2005. but the catch is that I need to keep Access as my front-end. One of the problems that I have faced (and there maybe more) but when it comes to running queries (especially Delete), I cannot seem to get things to work.

I would like to just store all of my data in SS2K5 and have all of my queries and forms run in Access.

Why is it that I am getting errors that do not seem to make sense (Access Error 3086) when I have opened up all of the permissions associated to my database?

Please let me know if anyone has any type of solution, or some place that I can go to that will help me understand this a little more.

my query is pretty simple.

DELETE Opsec_View.*
FROM Opsec_View;

View 1 Replies View Related

DELETE Query Syntax Using Subqueries

Jul 20, 2005

I have the following SELECT query, the results of which I would deletefrom the table they're pulled from:SELECT A.* FROM SalesOrderPartPrices AWHERE EXISTS(SELECT 'Exists' FROM SalesOrderPartPrices BWHERE(A.SalesOrderNo = B.SalesOrderNo) AND(A.PartNo = B.PartNo) AND(A.UnitPrice = B.UnitPrice) AND(A.RowID > B.RowID))However, when I try to run the following query, I get an error ("Msg170, Level 15, State 1, Line 1: Incorrect syntax near 'A'."):DELETE FROM SalesOrderPartPrices AWHERE EXISTS(SELECT 'Exists' FROM SalesOrderPartPrices BWHERE(A.SalesOrderNo = B.SalesOrderNo) AND(A.PartNo = B.PartNo) AND(A.UnitPrice = B.UnitPrice) AND(A.RowID > B.RowID))What am I doing wrong, and how do I resolve the issue? Any help isgreatly appreciated....... thanks in advance!-=Tek Boy=-

View 6 Replies View Related

Optimizing A Query To Delete Duplicates

Jul 20, 2005

I have a DELETE statement that deletes duplicate data from a table. Ittakes a long time to execute, so I thought I'd seek advice here. Thestructure of the table is little funny. The following is NOT the table,but the representation of the data in the table:+-----------+| a | b |+-----+-----+| 123 | 234 || 345 | 456 || 123 | 123 |+-----+-----+As you can see, the data is tabular. This is how it is stored in the table:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 || 3 | a | 123 || 3 | b | 234 |+-----+-----------+------------+What I need is to delete all records having the same "Row" when there existsthe same set of records with a different (smaller, to be precise) "Row".Using the example above, what I need to get is:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 |+-----+-----------+------------+A slow way of doing this seem to be:DELETE FROM XWHERE Row IN(SELECT DISTINCT Row FROM X x1WHERE EXISTS(SELECT * FROM X x2WHERE x2.Row < x1.RowAND NOT EXISTS(SELECT * FROM X x3WHERE x3.Row = x2.RowAND x3.FieldName = x2.FieldNameAND x3.FieldValue <> x1.FieldValue)))Can this be done faster, better, and cheaper?

View 3 Replies View Related

Dynamic SQL Delete Query Problem

Aug 6, 2006

Hi all--Given a query that returns ~557K rows on SQL Server 2005 SP1:

SELECT distinct(a.[Import_Date]),b.[Import_Date],
a.[OS_USERNAME],b.[OS_USERNAME],a.[USERNAME],b.[USERNAME],
a.[TIMESTAMP],b.[TIMESTAMP]
FROM [DBA_AUDIT_SESSION] a, [DBA_AUDIT_SESSION] b
where a.[OS_USERNAME]=b.[OS_USERNAME]
and a.[USERNAME]=b.[USERNAME]
and a.[TIMESTAMP]=b.[TIMESTAMP]
and a.[Import_Date]<b.[Import_Date]

On a table that has the following definition:

TABLE [dbo].[DBA_AUDIT_SESSION](
[Import_Date] [datetime] NULL,
[INSTANCE_NAME] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOST_NAME] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OS_USERNAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USERNAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USERHOST] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TERMINAL] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIMESTAMP] [datetime] NULL,
[ACTION_NAME] [varchar](27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOGOFF_TIME] [datetime] NULL,
[LOGOFF_LREAD] [float] NULL,
[LOGOFF_PREAD] [float] NULL,
[LOGOFF_LWRITE] [float] NULL,
[LOGOFF_DLOCK] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SESSIONID] [float] NULL,
[RETURNCODE] [float] NULL,
[CLIENT_ID] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SESSION_CPU] [float] NULL

I would like to delete duplicate data from this table and keep only the data with the latest Import_Date. I came up with the following delete statement:

delete FROM [DBA_AUDIT_SESSION]
FROM [DBA_AUDIT_SESSION] a,
inner join [DBA_AUDIT_SESSION] b
on a.[TIMESTAMP]=b.[TIMESTAMP]
where a.[Import_Date]<b.[Import_Date];

The command parses successfully, but I get the following runtime error:
'Table DBA_AUDIT_SESSION is ambiguous.'

Does anyone have suggestions on how to fix this delete statement?

View 3 Replies View Related

DELETE Query On Windows Vista

Sep 18, 2007



I am having trouble with 3 delete queries in my c# application, two other Delete queries run fine. All the Delete queries run fine on Win XP, just not in Vista(Vista's User Account Control off). Here is the simple query
DELETE FROM [tableName] I get error SQL Execution error 3640. I get this error whether I am debugging in msvs2005, or running in the Query Designer or if I just run the application outside of msvs2005.
I can get a Delete Query to work if i use the key columns like
DELETE FROM Horse
WHERE (CODE = @p1) AND (Stable = @p2) AND (HorseName = @p3)


Hope some has an idea


If not is there any other way I can delete all rows from a data table?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved