SQL 2012 :: Delete Record From Table With ID And Rownumber
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
ADVERTISEMENT
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
Mar 17, 2008
I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.
Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime
Child Table: TBL_VERIFICATION
PK: Serial Number
Thanks,
Sean
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
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
Jun 29, 2004
I like to give First Name and Last Name in two different text box and then hit the delete button (command button). Then it will do a query to find the person and delete the corresponding record from the table. Any kind of help will be appreciated. Thank you.
View 1 Replies
View Related
Sep 28, 2015
I just wanted to delete a record from a filetable by "delete from dbo.DocumentStore where stream_id = 'A322276D-AE65-E511-8266-005056C00008'".
Then i received error 1934:
Meldung 1934, Ebene 16, Status 1, Zeile 578
Fehler bei DELETE, da die folgenden SET-Optionen falsche Einstellungen aufweisen: 'ANSI_PADDING'. Überprüfen Sie, ob die SET-Optionen für die Verwendung mit indizierte Sichten und/oder Indizes für berechnete Spalten und/oder gefilterte Indizes und/oder Abfragebenachrichtigungen
und/oder XML-Datentypmethoden und/oder Vorgänge für räumliche Indizes richtig sind.
Something that i made a mistake with the SET-option for 'ANSI_PADDING' with indexted views and/or calculated rows and/or filtered indeces ...
View 3 Replies
View Related
Apr 2, 2008
I have two tables;
Table A
id, name
101, jones
102, smith
103, williams
104, johnson
105, brown
106, green
107, anderson
Table B
id, name, city, state
101, jones, des moine, Idaho
103, williams, Corvallis, Oregon
104, johnson, Grand Forks, North Dakota
105, brown, Phoenix, Arizona
107, anderson, New York, New York
I need to delete records from Table A that are not in Table B. My front end is writen in .net and I am using Data Access Layer along with a Business Logic Layer for data interaction.
I have tried at least seven variations of joining, right outer join, left outer join resulting in wiping our the entire table or nothing at all; not to mention deleting the record that ought to remain and keeping the record that needs to be deleted!
In my BLL I tried to capture the rowsAffected for the deletion by using-without success.
Dim rowsAffected As Integer = Adapter.ID_Deletion(ID)
If rowsAffected = 1 Then
Exit Function
Else
Return rowsAffected = 1
End If
Please help.
MsMe.
View 9 Replies
View Related
May 28, 2015
I have this table:
with actividades_secundarias as (
select a.*, r.Antigo, r.Novo, rn = row_number()
over (PARTITION BY a.nif_antigo, r.novo ORDER BY a.nif_antigo)
from ACT_SECUNDARIAS a inner join
((select
[Code] ....
I want to make a delete statement like this:
select * into #table1 from actvidades_secundarias where rn>1
Delete from act_secundarias where act_secundarias.nif_antigo = #table1.nif_antigo and act_secundarias.cod_cae = #table1.cod_cae
But it seems that I cant delete like this.
View 5 Replies
View Related
Mar 27, 2008
Hi,
I create a matrix table with wizard. I want to write rownumber() as a new column near my column.
But when =Rownumber(nothing) it returns the value of the record in the dataset. If the data in the 56.row then
=Rownumber(nothing) =56
But in the preview of table it is in 1.row.
What can I do?
View 9 Replies
View Related
Jun 8, 2015
I have a delete trigger on one table. When I delete a single row in this table that deleted record will be recorded to a history table.But sometimes my delete will delete more than one record. In this case also only one record is being recorded into the history table.How can I record all the deleted records into the history table when multiple records are deleted with one single delete statement?
View 4 Replies
View Related
Mar 20, 2008
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table Employee?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what Ive been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a Branch? entry, basically each entry in branch? and Employee? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use ON DELETE CASCADE? or something?
Thanks
View 8 Replies
View Related
Nov 7, 2014
To see where is the problem I am trying to count rows in the database.First I create a table A with 2 columns namely tablename, rowbefore and rowafter and I insert records in it as below.
INSERT INTO A
SELECT TableName = o.name, '', Rows = max(i.rows) FROM sysobjects o
INNER JOIN sysindexes i ON o.id = i.id
WHERE xtype = 'u' AND OBJECTPROPERTY(o.id,N'IsUserTable') = 1
GROUP BY o.name
ORDER BY o.name
Then I update rowbefore with rowafter as below.
UPDATE A SET rowbefore = rowafter
Now I launch my application with update records in the database.Then I am trying to update rowafter with new records as below.
UPDATE A
SET rowafter = (SELECT max(sysindexes.rows) FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE xtype = 'u' AND OBJECTPROPERTY(sysobjects.id,N'IsUserTable') = 1 AND A.tablename = sysobjects.name)
Does this update really update my column rowafter or not?
View 2 Replies
View Related
Jun 16, 2015
I have a table with about 466 Million rows. In this table there is a int column called WeeksToRetain as well as a EventDate column containing the date the row was inserted. I am trying to delete all the rows that that should be deleted according to the WeeksToRetain. For example, if the EventDate is 5/07/15 with a 1 in the WeeksToRetain column the row should be removed by 5/14/15. I am not sure what days SQL considers the beginning and end of the week. However the core issue I am having is the sheer mass of deletions I must do and log growth.
So I am trying to do the delete in batches. More specifically I want to load a temporary table with a million rows, then use the temporary table to load a sub temporary table with 100,000 rows and join this temporary table to the table I want to delete from looping through 10 times to get 1 million. The Logging.EvenLog table which is the table I'm trying to purge has a clustered index on EventDate (ASC). I would like to run this in a schedule job with enough time between executions for log backups to run.
DECLARE @i int
DECLARE @RowCount int
DECLARE @NextBatchDate datetime
CREATE TABLE #BatchProcess
(
EventDate datetime,
ApplicationID int,
[Code] .....
View 9 Replies
View Related
Feb 10, 2014
I have a table (Billing Table) having multiple records for people having one record per person per each month.
How to get a list of the guys having record just for one month (say feb) and doesn't have any other months.
View 4 Replies
View Related
Feb 24, 2015
I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now.
ResumeSkill Skill
Id SkillId
SkillId Name
I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.
View 2 Replies
View Related
Aug 6, 2014
I am inserting a record in XYZ table(DB1). Through trigger it will update ABC table(DB2).
I am getting error when doing above thing. What are the roles to be set to user to avoid above problem.
View 3 Replies
View Related
Oct 19, 2014
I have a master table and i need to import the rows into the parent and child table.
Master table name is Flatfile_Inventory
Parent Table name is INVENTORY
Child Tables name are INVENTORY_AMOUNT,INVENTORY_DETAILS,INVENTORY_VEHICLE,
Error details will be goes to LOG_INVENTORY_ERROR
I have 4 duplicate rows in the Flatfile_Inventory which i have already inserted in the Parent and child table.
Again when i run the query using stored procedure,its tells that all the 4 rows are duplicate and will move to the Log_Inventory_Error.
I need is if i have the duplicate rows in the flatfile_Inventory when i start inserting into the parent and child table the already inserted row have the unique ID i must identify it and delete that row in the both parent and chlid table.And latest row must get inserted into the Parent and child table from Flatfile_Inventory.
-- ===============================================================================================
-- STORED PROCEDURE FOR FLATFILE_INVENTORY
-- ===============================================================================================
USE [IconicMarketing]
---=======================================SALES_CURSUR===========================================
--USE IconicMarketing
--GO
DECLARE
@FileType varchar(50) ,
@ACDealerID varchar(50) ,
[code].....
View 2 Replies
View Related
Feb 6, 2015
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?
View 6 Replies
View Related
Feb 1, 2007
Hi seniors
there are two tables involve in replication let say table1 and replicated table is also rep.table1.
we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.
plz let me know where am i wrong and how i put it into right way.
there is no triggers on published tables and noother trigger is created on published table.
regards
Ahmad Drshen
View 6 Replies
View Related
Sep 24, 2015
I have a table where I need to delete rows if the member does not exists in the member table. Will the following syntax work ?
Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL
View 9 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
