Delete Multiple Rows With 2 Ids
Aug 9, 2006
Hello All,
I have a table:
idSurrogate int identity
id1 int
id2 int
id1 + id2 is a unique index
I need to delete multiple rows from the table given a list of id1 and a list of id2
In other words
@id1List = '10,20,30'
@id2List = '1,3,5'
I need to delete these 3 rows from the table
1) @id1=10 and @id2=1
2) @id1=20 and @id2=3
3) @id1=30 and @id2=5
I am a bit lazy today - can anyone help out with a delete sql stmt
Thanks!
View 11 Replies
ADVERTISEMENT
Oct 23, 2007
I have an SQL 2000 server. I have multiple tables in the db that have a row with a time stamp of '10-23-2007'. What I am trying to do is delete these specific rows because they don't belong.
So I need to query the db for table names that are like 'elect_Sub%' and then execute a query on those tables that would delete the row with the time_stamp '10-23-2007'. I know that I have to use the db schema to get the table names, but I need help in writing the sql script that will automatically scroll through the tables.
Thanks,
View 6 Replies
View Related
Aug 28, 2007
Hi,
I have the following scenario :
CustomerDetail
customerid
customername
status
app_no
[status = 0 means customer virtually deleted]
CustomerArchive
archiveno [autoincrement]
customerid
customername
status
At the end of the month, I have to physically delete customers. I have written two stored procs:
proc1
create proc spoc_startdeletion
as
declare @app_no int
select @app_no = (select app_no from customerdetail where status=0)
EXEC spoc_insertcustomerarchive @app_no
-- After transferrin, physically delete
delete from customerdetail where status=0
proc2
create proc spoc_insertcustomerarchive
@app_no int
as
insert into customerarchive(customerid,customername,status)
select customerid,customername,status from customerdetail where app_no = @app_no
It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'
How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred.
Vidkshi
View 15 Replies
View Related
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
Aug 20, 2007
I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:
DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)
The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.
I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.
Does anyone have suggestions on what might cause this sort of behavior?
View 3 Replies
View Related
Mar 12, 2007
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
View 3 Replies
View Related
Jul 24, 2015
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 3 Replies
View Related
May 7, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
View 6 Replies
View Related
Jan 20, 2006
What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)
delete dbo.tblcase
where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseclient
where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseinformation
where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaselawyer
where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseprosecutor
where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
View 1 Replies
View Related
Dec 25, 2005
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
View 1 Replies
View Related
Jun 4, 2007
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ). The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); }
/* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
View 1 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Aug 17, 2015
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies
View Related
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
Apr 23, 2008
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
View 3 Replies
View Related
Mar 2, 2015
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
View 3 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Feb 12, 2015
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I need results for each parent like this
ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]
View 1 Replies
View Related
Aug 14, 2015
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
View 3 Replies
View Related
Sep 26, 2007
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
View 1 Replies
View Related
Oct 15, 2009
is there a way to update multiple rows in one update query in tsql? what I wanted to do is for example I got a table containing
code : desc
1 : a
2 : b
3 : c
4 : d
1 : e
3 : f
I wanted to update it to
code : desc
1 : x
2 : b
3 : y
4 : d
1 : x
3 : y
how to do it?
View 5 Replies
View Related
Mar 17, 2006
Hi All,I am designing a purge process for a db that has grown to almost 200GB.My purge process will remove about 1/3 of the 500 million rows spreadover seven tables. Currently there are about 35 indexes defined onthose seven tables. My question is will there be a performance gain bydropping those indexes, doing my purge, and re-creating the indexes. Iam afraid that leaving those indexes in place will create a lot ofextra overhead in my delete statements by having to maintain theindexes. I know that it could take many hours to rebuild the indexesafterward, but I am planning on doing that anyway. The reason that Iwant to know whether I should drop the indexes ahead of time, is I maynot be able to do the entire purge at once and the tables may need tobe accessed between purges. If this occurs, I will need to have thoseindexes in place.So do I drop the indexes before the purge and re-create them later ordo I leave them in place and re-index them afterward?Thanks In Advancep.h.
View 12 Replies
View Related
Oct 19, 2006
Hi
I am trying to delete rows in temp1 which are exist in temp2
delete from dbo.temp1 as a
inner join temp2 as b
on a.regionname=b.regionname
and a.servicecode=b.servicecode
and a.directioncode=b.directioncode
and a.destorigflag=b.destorigflag
I am getting error :
Incorrect syntax near the keyword 'as'.
thanks in advance
subash
View 3 Replies
View Related
Oct 17, 2007
Hi there,
I have two tables a,b, with the same columns.
How do I delete all the data from a that has the same entry as b?
Thanks for all the answers
View 3 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Dec 17, 2007
I have a table that I would like to only keep the top 10 rows for each username. How can I kep the top 10 and delete the rest?
View 17 Replies
View Related
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
Sep 6, 2004
Hi..
DELETE FROM table1 WHERE projektID=5
there are 500000 rows that has projektID=5.. and when i run the query the hardrive is working for a couple of minutes and then stops. and NOTHING has happened. not a single row has been deleted?.. cant the DELETE statement handle that many rows or?. or is there another way i can delete these rows?.
View 3 Replies
View Related
Apr 6, 2006
Hii want to delete a row in my database but the problem is, i cant delete it as other table rows is linked to it. I have to delete all the rows thats linked to the row i wanna delete first.Is there a easier way to delete the row and all the rows thats linked to it? i wanna code it to do it.an suggestions?
View 1 Replies
View Related
Jun 25, 2000
I have a table which looks as follow:
field1 field2 field3 field4 field5 ......
A B C A X ......
A B C B Y ......
A B C C Z ......
A B C A Y ......
. . . . . ......
I want to delete all the rows except one row. Anybody can help?
Thank you very much.
View 3 Replies
View Related
Jan 20, 2000
How do you delete duplicate rows in a table so only one row is left in the table, using T-SQL.
View 1 Replies
View Related
Mar 30, 1999
CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS
IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW
TO GET YESTERDAY DATE BY USING ISQL WINDOW.
THANKS
JK
View 2 Replies
View Related
