Delete Record Selection Question
Sep 28, 2005
I'm having trouble with the below sql command. What I'm trying to
do is to delete records from tblPhotoHeader table where there are no
corresponding child records in tblPhoto.
The select statement works correctly, I'm just not sure about how to
apply the syntax to correctly select the records in the delete
statement.
Any help from the experts here would be helpful.
Thanks
Tom
---------------------------------------------------------------------
DELETE FROM tblPhotoHeader
WHERE Exists
(SELECT tblPhotoHeader.photoid, photoOrderID,
tblPhoto.photoType
FROM
tblPhotoHeader LEFT OUTER JOIN
tblPhoto ON tblPhotoHeader.photoID = tblPhoto.photoID
WHERE (tblPhotoHeader.photoOrderID = 143)
AND (tblPhoto.photoType IS NULL))
View 1 Replies
ADVERTISEMENT
Jul 9, 2007
hello members
i want to no that weathere there is any procedure to select come random records from the database
for eg i want to select 10 students out of 100 randomly
is there any query for this in SQL server 200
View 2 Replies
View Related
Apr 26, 2007
I need to pull a report that gives me the desired results as show below. I first show the data that is in the table, then below that is the desired result.
What I am trying to do is pull every employee's most recent 3 appraisal Effective Dates within the last 4 years where Rating is different than X.
Sorry I don't reall know how to explain this using code because I seem to be having a block right now. Not sure where to start really.
Any help would be greatly appreciated!! Thanks again!
Records in tblAPPRAISALS Table:
Employee | Appraisal Effective | Rating
A | 1/10/2006 | 5
A | 1/10/2005 | 4
A | 1/10/2004 | 5
A | 1/10/2003 | 5
A | 1/10/2002 | 5
B | 1/10/2006 | 5
B | 1/10/2005 | 5
B | 1/10/2004 | X
B | 1/10/2003 | 5
C | 1/10/2006 | 4
C | 1/10/2005 | 5
C | 1/10/2004 | 5
My current SQL:
SELECT DISTINCT TOP 3 Employee, Appr_Eff as [Appr Effective], Rating_Of_Rcd as [Rating]
FROM tblAPPRAISALS
WHERE Appr_Eff >= DateAdd("YYYY", -4, Date()) AND Rating_Of_Rcd <> "X"
Order by 1, 2 DESC
Results from this Query:
Employee | Appraisal Effective | Rating
A | 1/10/2006 | 5
A | 1/10/2005 | 4
A | 1/10/2004 | 5
Desired Results:
Employee | Appraisal Effective | Rating
A | 1/10/2006 | 5
A | 1/10/2005 | 4
A | 1/10/2004 | 5
B | 1/10/2006 | 5
B | 1/10/2005 | 5
B | 1/10/2003 | 5
C | 1/10/2006 | 4
C | 1/10/2005 | 5
C | 1/10/2004 | 5
View 5 Replies
View Related
Feb 27, 2006
Hi,I need some advise on how to automate record selection on a storedproc. Here is my situation. I have a stored proc that I used onCrystal reports with two parameters - Acctcode and Subacct. When auser enters ' *' on these parameter, it means to report on allaccounts otherwise, report only on specific account.Here is my select statement with line numbers:Create proc rb_SubledgerRpt@Acctcode varchar(4), @SubAcct varchar(3)As3 Select AcctCode, SubAcct4 From GLDetails5 Where SubAcct <> ' '6 and AcctCode = @Acctcode -- for specific acctcode7 and SubAcct = @SubAcct -- for specific subacct8 Go-- If a user wants to see all Acctcode, and all Subacct, how do Idisable lines 6 and 7?Thank you in advance for your help.Edgar
View 6 Replies
View Related
Nov 17, 2006
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
DELETE FROM Broker_Rates
WHERE (Broker_Rates.BID <> Broker.BID)
Thanks
View 6 Replies
View Related
Jul 20, 2005
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies
View Related
Jul 31, 2000
I accidently made a duplicate record and I am unable to delete it. Everytime I try to delete, it gives me an error message saying:
Insufficient oe incorrect key coloumn information; too many rows affected by the update.
I am unable to delete either records. Is there another way of accomplishing this task. Thanks for your help in advance.
Bud
View 1 Replies
View Related
Jan 1, 2006
Hello i am new to MS SQL. Just moved in from MS Access.
I used to program in VB6.
mY Question is when i attemp to delete a record the followin error message displays
"Key column information is insufficiant or incorrect.Too many rows were affected by the update."
I have put the as code for the 'DELETE Record' command button in VB6 as follows.
adomain.Recordset.Delete adAffectCurrent
Please help me to safley delete a record from the SQL database using VB6.
View 4 Replies
View Related
Dec 14, 2007
Hi,
How can i delete a record from a table . table contains approx 3 million data and when i execute a query for delete it give error message from transaction log space problem.
Is there any way to stop the log in transaction log and delete the record.
thanks
View 5 Replies
View Related
Jul 20, 2005
please helpi was trying to delete a specific record on a table but it still showsthe same record that should be deleted. i dont know how.
View 3 Replies
View Related
Jul 22, 2007
whats the best way to delete a parent record? for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?my code below: The T-SQL Stored ProcedureALTER PROCEDURE dbo.DeleteInventory
(
@InventoryID int
)
AS
DELETE FROM Booking
WHERE InventoryID = @InventoryID
DELETE FROM Inventory
WHERE InventoryID = @InventoryID
The code in the Code Behind to execute when a button(delete) click Try
Dim temp As Integer = SqlDataSource4.Delete
If temp > 0 Then
lblDeleteDtatus.ForeColor = Drawing.Color.Blue
lblDeleteDtatus.Text = temp & " Records/Rows Deleted."
Else
lblDeleteDtatus.ForeColor = Drawing.Color.Orange
lblDeleteDtatus.Text = "No records Deleted!"
End If
Catch ex As Exception
lblDeleteDtatus.ForeColor = Drawing.Color.Red
lblDeleteDtatus.Text = "An Error Occured.<br />" & ex.Message.ToString
End Try any better methods anyone wants to share?
View 2 Replies
View Related
May 28, 2004
OK,
This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:
DELETE FROM Invoices WHERE InvoiceID = 153345
Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.
TIA
View 12 Replies
View Related
Jun 6, 2008
Hi, I tried to delete a record in tblA which has an ID in tblB, i got error msg saying referential problem, please tell me what should i do? thanks.
View 2 Replies
View Related
Oct 23, 2006
Hi ,
How can i delete the duplicate record from a table
use Northwind
create table Emp (Ecode char(2), Ename char(10))
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A4','D')
Insert into Emp(Ecode, Ename) values('A4','D')
select * from emp order by Ecode
Thanks
ASM
View 7 Replies
View Related
Jan 22, 2008
Hi everyone... I have a problem on deleting a record. I accidentally duplicate the record.
I need to delete other records and left only 1 record based on each date and employeenumber.
supposing i have a table hrempshiftschedule
i have 3 fields... empno, shiftdate, and shifttype
sample data...
empno shiftdate shifttype
1 1/1/2008 S1
1 1/1/2008 S1
2 1/1/2008 S2
2 1/1/2008 S2
2 1/1/2008 S2
2 1/2/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5
expected output....
empno shiftdate shifttype
1 1/1/2008 S1
2 1/1/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5
pls help and guide me.... thanks
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
View 6 Replies
View Related
Feb 22, 2008
how can i recover delete record from a table. is possible to find who delete record from a table.
View 3 Replies
View Related
Apr 7, 2008
Hi i have a very annying problem that i cant seem to solve by myself. I have developed a content managment system for a webpage where people can manage the page. It's almost done except for the fact that i cant seem to delete records from my sql express database.
To access the database i use an sql login in my code to delete witchever record is retrived from a querystring in the URL field. Below is a sample of my delete code when a button is pressed. protected void ButtonDelete_Click(object sender, EventArgs e)
{
string dID = Request.QueryString["dID"];
string myConnectionString = @"Data Source=SRVWEBSQLEXPRESS;Initial Catalog=se;User ID=xx;Password=xx";
SqlConnection myConnection = new SqlConnection(myConnectionString);
string myDeleteQuery = "DELETE FROM drift WHERE dID = @dID";
SqlCommand myCommand = new SqlCommand(myDeleteQuery);
myCommand.Parameters.AddWithValue("dID", dID);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
Response.Redirect("list.aspx");
}
When the i run the code on my development machine located at 10.12.0.80 and the server is located at 10.12.1.65 and this is where the databse is located. The strangest thing is that when i press my deletebutton while debugging in VS2008 on my devmachine the record is deleted! BUT when i run the code live on the server i get an error. See the below log file taken from the windows 2003 server application log.
Event Type: InformationEvent Source: ASP.NET 2.0.50727.0Event Category: Web Event Event ID: 1314Date: 2008-04-07Time: 10:26:45User: N/AComputer: SRVWEBDescription:Event code: 4011 Event message: An unhandled access exception has occurred. Event time: 2008-04-07 10:26:45 Event time (UTC): 2008-04-07 08:26:45 Event ID: 8bdda96aeee44448b570891c593bdb3e Event sequence: 242 Event occurrence: 1 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1015505475/Root-1-128520196339603398 Trust level: Full Application Virtual Path: / Application Path: C:wwwwebsite Machine name: SRVWEB Process information: Process ID: 5156 Process name: w3wp.exe Account name: NT AUTHORITYNETWORK SERVICE Request information: Request URL: http://website/cms/drift/editdrift.aspx?dID=19 Request path: /cms/drift/editdrift.aspx User host address: 10.12.1.1 User: webmaster Is authenticated: True Authentication Type: Forms Thread account name: NT AUTHORITYNETWORK SERVICE
Could anyone help me solve this problem. Thanks.
View 3 Replies
View Related
Dec 17, 2004
If I script the SQL statement with a constant, deleting the record from the database works.
If I script the SQL statement to delete based on the WHERE clause being a variable name, it will not delete the record.
The value being compared in the WHERE clause comes directly from the Sequel database.
I have a dropdown box that is filled from the database. The dropdown1.selecteditem.text is placed in a variable. The script is to delete a record from the database where the table.name in the database equals the item name selected from the dropdown box.
This querystring does not delete the record from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'variableCompareText')"
This querystring does delete the recrod from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'John')"
Why can't I delete a record with a variable in the querystring? Otherwise, you would have to always know in advance which record to specify rather than being deleted dynamically.
View 2 Replies
View Related
May 6, 2006
Well, I really messed up. Instead of changing the name of a current company record in a table I changed ALL the company names in the table. Me.CustomerDataSource.SelectCommand = "UPDATE tbl_customers SET company = '" & companyTextBox.Text & "'"
So, I need to insert a WHERE clause to fix this. My problem is that I've been searching everywhere for this simple command structure and cannot find anything that specifically addresses a simple way to reference the current record.
I tried...Me.CustomerDataSource.SelectCommand = "UPDATE tbl_customers SET company = '" & companyTextBox.Text & "' WHERE recno = @recno"
But I get the error:
Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@recno".
Can anyone provide this simple query clause?
View 2 Replies
View Related
Feb 25, 2008
Hi,
I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.
So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:
select * from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier
So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.
Obviously something like this won't work:
delete from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier
Though it would be nice if it did.:D
So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.
Can anyone render any assistance on this one???
I would certainly appreciate it.
Thanks.
View 1 Replies
View Related
Mar 26, 2008
Can anybody help me in writing a SQl trigger to delete a record when inserted after 70 seconds.?
View 20 Replies
View Related
Jul 19, 2007
Hi.I have a "union" table which results of a union of two tables.Occasionally I could have duplicates, when the same PIN has been addedto both tables, albeit at different Datees/Times, such as:PINNameAdded Date100411A7/11/2007 10:12:58 AM100411A7/17/2007 10:54:23 AM100413B7/11/2007 10:13:28 AM100413B7/17/2007 10:54:39 AM104229C7/6/2007 2:34:13 PM104231D7/6/2007 2:34:25 PM104869E6/10/2007 11:59:12 AM104869E6/22/2007 2:40:18 PMThe question is - how can I delete by queries the first occurence(time-wise) of these duplicates - i.e. I would want to delete thefirst occurence of 100411 (A), the first occurence of 100413 (B), andthe first occurence of 104869 (E) in the example above - records C andD show only once, so they are fine.Is there a MsAccess solution ? Is there a SQL-server solution ?Thank you very much !Alex
View 2 Replies
View Related
Apr 8, 2008
In a C++ project, I implement a component of accessing database with programming in ole db com interface.
I delete many rows in following style:
IRowsets::RestartPosition( NULL );
IRowsets::MoveNext( )
do
{
IRowsetsChange :: DeleteRows( DB_NULL_HCHAPTER, 1, &(m_hRow), &nStatus );
}while( IRowSet::MoveNext( 0 ) );
The Problem: In this style, I only delete two records. while use ' IRowSet::MoveNext( 0 ) ' to get the third records, the result is END_OF_RECORDSET.
Why I only delete two rows? Why I do not get the third row?
Thank you!
View 3 Replies
View Related
Oct 31, 2006
I'm facing problem when delete a record in the ms sql 2005 .
The error message i got is stated below:
No rows were deleted
A problem occured attempting to delete row 1304.
Error: .Net Sql Clint Data Provider
Error Message: Possible index corruption detected. Run DBCC CHECKDB.
Correct the errors and atempt to delete the row again or press ESC to cancel the changes.
just certain record i can not delete and face this problem.
i detach the database and attached in the ms sql 2000, and select the same record to delete. there is no problem at all.
i wander is it ms sql 2005 bugs? can anyone tell me how to fix it .. how to to use the DBCC CHECKDB ??
thanks alot....
P/s : if i posted at the wrong section just let me know, I'm beginner in this forum here ...
thanks...
View 18 Replies
View Related
Sep 28, 2007
Hi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!
View 6 Replies
View Related
Oct 3, 2000
Dear friends,
I am thinkking there has to be away to do this in a SQL
statement.
can't you do some kind of...
Create Procedure sp_update_users
@User_Id Int,
@Title_Id Int
AS
UPDATE USERS
DELETE WHERE @User_ID = User_Id and @Title_Id = @Title_Id
ELSE
INSERT INTO USERS VALUES (@User_ID,@Title_Id;)
View 3 Replies
View Related
Aug 6, 2004
Hi ,
I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time?
For example if I've two tables involved by join
DELETE <...> from Customers A
INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID
I Must use two statement to remove records from both the tables?
Thx
View 3 Replies
View Related
Jun 14, 2007
Hi There, I have created a trigger which supposingly will do event before delete the record from its own table.unfortunately when i try delete the record to test it whether it will do the event (inserting some records to another table), i found that it was not doing the event like i wanted to be. :(the trigger is as below :=======================CREATE TRIGGER TG_D_AGENT ON dbo.AgentFOR DELETEASbegindeclare @vAgentID as numeric,@vAgency as varchar(50),@vUnit as varchar(50),@vAgentCode as varchar(50),@vName as varchar(50),@vIC as varchar(14),@vAddress as varchar(100),@vContactNumber as varchar(50),@vDownlink as varchar(50),@vGSM as varchar(10),@vAM as varchar(10),@vDeleted_date as datetime set @vDeleted_date = convert(datetime, convert(varchar(10) , getdate(),103),103)declare cur_policy_rec CURSOR forselect AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from insertedopen cur_policy_recfetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM WHILE @@FETCH_STATUS=0BEGIN INSERT INTO [Agent_history] (AgentID,Agency,Unit,AgentCode,Name,IC,Address,Con tactNumber,Downlink,GSM,AM,Deleted_date) VALUES(@vAgentID,@vAgency,@vUnit,@vAgentCode,@vNam e,@vIC,@vAddress,@vContactNumber,@vDownlink,@vGSM, @vAM,@vDeleted_date)fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM enddeallocate cur_policy_recend===============================in oracle , i normallly can do something like this...====================================CREATE TRIGGER TG_D_AGENT ON dbo.AgentBEFORE DELETE ON dbo.Agent FOR EACH ROWbeginIs that such thing function like 'BEFORE' in MS SQL SERVER 2000, coz in sql server im not sure they do have or not. Plz someone help me on this...realy appreciated if can!
View 1 Replies
View Related
Mar 17, 2014
I will try my best to explain this, We have a shopping cart on our website, the person that was developing this has now left the company and I've been given the job to finish it off.
When I load all the items that the user has entered in to his/her cart I return the Item ID and the RowNumber (ROW_NUMBER() OVER (Order by Id) AS RowNumber)
I'm trying to delete the item from the table using the following query
DELETE FROM [dbo].[Cart.Items] WHERE UniqueID = UniqueID and ItemID = @ItemID and @RowNumber IN (
SELECT ROW_NUMBER() OVER (Order by Id) AS RowNumber)
Now the reason we are using the RowNumber is because the user can add the same Item as many times as they like so for example you buy 3 different mobile phones, and you want three screen protectors, they will click screen protector 3 times which will add 3 records in to the db with the same id. so the row number is used to find the correct one.
But the above delete is not working.
View 1 Replies
View Related
May 9, 2008
Hi All,
I have just one corrupted record on a table: I copied everything else to another table but I can't delete or rename the old one!
Is restoring the whole DB my only way out there?
Any help or suggestion would be much appreciated!
Cheers
View 8 Replies
View Related
Mar 29, 2007
Somehow I got the value -1.#IND into several records in a table in SQL 2000 DB.
I assume that it is a kind of €œNot A Number€??
The problem I am facing now, is that I can not modify the value to a legal value. Nor can I delete the record that holds the number. I get the error message:
€œAnother user has modified the contents of this table or view, the database row you are modifying no longer exists in the database.
Database error: €˜[Microsoft][ODBS SQL Server Driver][SQL Server] A floating point exception occurred in the user process. Current transaction is cancelled.€?
Any suggestions or comments?
View 4 Replies
View Related
Nov 17, 2006
Hi, I want to delete from table when the record count is over 5000. That is, the table's record cannot over 5000 count. How? row_number() seem to be used...
View 11 Replies
View Related
Feb 7, 2005
HI, I have a table similiar to this:
Division Type Section Location ModificationDate
------- ----- ------- -------- ---------------
3 4 2 Los Angeles 2/1/05
3 4 2 New York 2/4/05
4 5 1 Los Angeles 2/4/05
3 4 2 Seattle 2/7/05
4 5 1 Dallas 2/6/05
3 4 4 London 2/3/05
I need to remove duplicate records that have the same division,type,section pair by slected the most recent modification date and keeping the data in the rest of the columns. The results of what I want to do would look like:
Division Type Section Location ModificationDate
------- ----- ------- -------- ---------------
3 4 2 Seattle 2/7/05
3 4 4 London 2/3/05
4 5 1 Dallas 2/6/05
Does anyone have idea how I would do something like this? Thanks.
View 4 Replies
View Related
