Transact SQL :: How To Delete Row With Condition
Aug 6, 2015
create table target
(Taid int, studenid int, std int,div int,year int ,ActivityId int)
insert into target values(1,1,1,2015,1),
(1,1,1,2015,2),(1,1,1,2015,3),(1,1,2,2015,1)
,(1,1,2,2015,4) ,(1,1,2,2015,3);
create table activity(ActivityId int,Activity nvarchar)
insert into activity values (1,'sport')
,(2,'swim')
,(3,'read')
,(4,'dace');
I want to edit(update,delete,insert) target table activity from front end for that I took grid view and that grid all activity come of single student studenid(unique key for each student) that you see design below like this
that activity present in target table that come as check in check and that activity not select student from activity activity table that come unchecked
I want to update above information means unchecked alredy check data should delete and new check data add , already check data update
I have use merge statement but problem is that at single time only one row execute that is check because data come from data gridview.
View 5 Replies
ADVERTISEMENT
Oct 17, 2015
I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :
create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc
[Code] .....
View 3 Replies
View Related
May 7, 2008
Hi to all:
I'm trying to make a sp who generates an export string for everey sql command, for example: Insert, update and delete. In the case of Insert I just have the sp, but in "update" and "delete" commands... how can I know the conditions?..
UDTATE DemoTable SET DemoField=77 WHERE (Condition1 AND Condition2)
....may I use a comparative with 'updated' table?, please help me with your ideas
Thanks a lot.
View 4 Replies
View Related
Feb 18, 2014
I have written a merge Statement where i am facing trouble to delete the data basing on Where Clause Condition.
1) Case 1 : For example i have inserted Data from Source to Target based on Date Key Condition.Take an Instance 10 Records Inserted.
2) Case 2 : For example some changes in the records and it has been updated through the Merge Statement .
3) Case 3 : For the Same Date key based on Conditions now three records has came and need to be inserted and rest should be deleted for that Date Key.
How i need to proceed on this before 10 records are not getting deleted and new records adding for that one
My Example Code :
MERGE INTO TargetTable AS Target
USING (
SELECT DISTINCT Col1,
Col2
FROM Table1 AS cga
)
ON dad.AnchorDate = CASE
[Code] ....
View 1 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
Mar 19, 2014
create table #sample
(
Name varchar(100),
value int
[code]....
From that I wanted to delete some records based on following condition. randomly select any number of records but sum(value) = 125 and name = xxx
View 2 Replies
View Related
Jul 10, 2015
I am in assumption that we cannot do NULL value comparison in where clause directly (results will not be proper) we have to use ISNULL. Is my understanding correct? Is using ISNULL efficient when we have to this operation for large volume?
View 5 Replies
View Related
Jul 24, 2015
I have an order with the time 08/03/2015 7:30 08/03/2015 10:30 ..I have availability with the time 08/03/2015 07:00:00 to 08/03/2015 19:30:00...Here the availability falls for 3 hours only..My requirement is that For order if availability< 4hours i don't want to show employee.How to apply the condition.
View 7 Replies
View Related
Apr 14, 2015
I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
View 3 Replies
View Related
Jul 4, 2015
I have a table called names (firstname, lastname, number) then i have a DECLARED variable called @displaynum...If @displaynum id true my select query should select the number column. if the variable is false , only the first two columns are selected.
e.g @displaynum = true
@displaynum = false
Firstname | Lastname | number Firstname | Lastnam
blah | blah | 12345
blah | blah
i am not sure if this is possible as a case still selects the column but just changes the values.
View 12 Replies
View Related
Sep 16, 2008
How can I update only one row doing an update query?
For example:
update from table1
set category = 'C'
where country = 'Brazil'
One want to update only one row with country = 'brazil'
View 5 Replies
View Related
Apr 27, 2015
I have table with filed
EmpProject -Table
Empno, Name, Project NO, cost center
I need to restrict the results by project No by user input. In case if user did not provide any value for the projectNO, then need to fetch all rows.
Select empno, name, projecno, cost_center from empproject where projectno=nvl(:pno, :deptno)
View 6 Replies
View Related
Nov 22, 2015
I have a question about SQL Server.
Table patient:
create table patient (pn int,code int,date date,doctorcode int)
insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19','100),
(1,10,'2015-02-19','101),
(1,10,'2015-02-19','102),
[Code] ...
Table Patientref:
create table patientref
(pn int,code int, sdate date,edate date,status int)
insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-19','2015-03-24',2),
[Code] ...
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Examples: patient
pn | code | date | doctorcode
2 | 10 |2015-02-12 | 101
2 | 10 |2015-02-13 | 102
2 | 10 |2015-02-14 | 103
Table : Patientref:
pn | code | sdate | edate | Status
2 | 10 |2015-02-08 | 2015-02-19 | 4
2 | 10 |2015-02-09 | 2015-02-19 | 2
2 | 10 |2015-02-10 | 2015-02-19 | 2
2 | 10 |2015-02-11 | 2015-02-18 | 1
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon
Based on this, the desired output is below:
pn | code | date | doctorcode | sdate |edate |status
1 | 10 |2015-02-19 | 100 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 101 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 102 |2015-02-19 |2015-03-24 | 2
2 | 10 |2015-02-12 | 101 |2015-02-10 |2015-02-19 | 2
[Code] ...
I tried it like this:
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
order by case when pr.status=2
then 1 when pr.status=4 then 2
when pr.status=3 then 3
when pr.status=1 then 4 end ,pr.sdate
)pr
but this query not given expected result.here when dos not fall between sdate and edate that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.
View 7 Replies
View Related
Sep 29, 2015
I have an existing store proc with insert and update statements, I want to add a condition at the top of the proc with conditions like;
declare @dayofweek int
set datefirst 1
select @dayofweek = datepart(dw,getdate())
--select @dayofweek
if @dayofweek = 1
[Code] ...
If the condition meets with either of the above 2 conditions, than have to run the actual store proc.
My actual Storeproc in which I need to incorp the above conditions is:
CREATE PROCEDURE [dbo].[Load_Product]
@FileDate date = NULL
AS
BEGIN
DECLARE @EventText varchar(500),
@Rows int = 0,
@RowsTotal int = 0,
[Code] .....
View 12 Replies
View Related
Jul 3, 2015
I am using SQL Server 2008.Each stock item will have default 4 document type (1, 5, 6, 7) and each will have 3 zone's (1, 2, 3) to qualify. Each zone will be updated to 1 for that document type if the item successfully pass through it. If all zone are NULL means no transaction. How to retrieve only the failed rows which means not all zone are 1 or NULL.In the image GJ-00064 has one row failed. So how to get the count of failed rows for each item
Expected result:
Uniid <-> Stockcode <-> FailedRows
1670 <-> GJ-00064 <-> 1
View 8 Replies
View Related
Jul 16, 2015
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
View 15 Replies
View Related
Nov 6, 2015
below is my original query
select Value = count(*) from dbo.test
I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.
View 5 Replies
View Related
Jun 1, 2015
I want to calculate year to month data for month wise . Ihave start range and end range table like below
Start range and end range table
Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015
[Code] ....
Then I want to pass this value one by one in below table for get value month wise
Main table
Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404
[Code] ....
The below query is for november month: (Apr to Nov)
select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)
So I want to pass second row
select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'
Like wise I want to get month wise data of YTM data.
My expected output is
201411
201412
201501
201502
201503
201504
201505
[Code] ....
View 10 Replies
View Related
Jun 3, 2015
I need to write a query on a table..
table defination .. table Client_Master
file_id int identity(1,1) primary key
, client_id int
,LOAN_SANCTION_DATE datetime
,is_reject bit
has Data ..
FILE_ID CLIENT_ID
LOAN_SANCTION_DATE IS_REJECT
21139 22784
2014-02-03 00:00:00.000 0
21141 22784
2014-02-03 00:00:00.000 NULL
20869 22784
2014-02-03 00:00:00.000 1
20870 22784
2014-02-03 00:00:00.000 0
21571 22784
2014-02-03 00:00:00.000 1
66056 22784
2014-02-03 00:00:00.000 1
Above has 6 files entries for client id 22784 and LOAN_SANCTION_DATE 2014-02-03 out of which 3 are rejected ..
Now , i want to write a query to select those distinct client_id , LOAN_SANCTION_DATE from Client_Master where all files has been rejected ..
means by grouping client ID and LOAN_SANCTION_DATE all the files are rejected ..
I have wrote as below .. got the result but not satisfy with the query
SELECT DISTINCT CLIENT_ID,LOAN_SANCTION_DATE,COUNT(FILE_ID) AS No_Of_Files
,COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )AS No_Of_Rejected
FROM dbo.FILE_MASTER
GROUP BY CLIENT_ID ,LOAN_SANCTION_DATE
HAVING
COUNT(FILE_ID)=COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )
View 4 Replies
View Related
Sep 22, 2015
I've two audit tables, AUDIT_ORDERS and AUDIT_ORDER_LINES.
The AUDIT_ORDERS has these columns: AUDIT_ID, ORDER_ID, AUDIT_DATE and other ones.
The AUDIT_ORDER_LINES has these columns: AUDIT_ID, ORDER_ID, ORDER_LINE_ID, AUDIT_DATE and other ones.
I need to join these two tables in order to select for each order line row the first order having the related audit date lower than or equal to the audit date of the related order line.
I don't want to use the TOP 1 clause or a subquery. I think to complete a such statement:
SELECT OL.Order_Line_ID, O.Order_ID, OL.Audit_Date, O.Audit_Date
FROM AUDIT_ORDER_LINES as OL INNER JOIN AUDIT_ORDERS as O
on OL.Order_ID = O.Order_ID and O.Audit_Date <= OL.Audit_Date ...
I'd like to get the first row of the Audit_Orders with audit_date <= of the audit_date of the Audit_Order_Lines table by using the join clause.
View 9 Replies
View Related
Jun 17, 2015
I have a query where i would calculate the counts of three Servers
declare @Managed float
declare @Active float
DECLARE @Values varchar(1000)
SET @Values = 'WE,EE,CO'
select @Managed = count(Name0) from v_R_System where Operating_System_Name_and0 like '%Workstation%'
[Code] ...
Here i need output like below
Workgroup Managed Active
'WE' 255 ,400
'EE' 300 ,450
'CO' 155, 600
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
View 3 Replies
View Related
Dec 1, 2015
I have a Stored Procedure, wherein I need to use a while loop. essentially the loop looks like this.
Set @Kount = 1
--TestScript
Select 'TempReqTable', * from @TempReq
WHILE Exists(Select * from @TempReq WHERE TempID = @Kount)
BEGIN
--Do stuff with values from table
Set @Kount += 1
END
The test script confirms that there is only one row in the @TempReq table. However the loop never stops running and @Kount keeps being incremented and incremented until I manually stop the execution of the SP.
Is there some rule that I am not aware of that does not allow the use of an Exists statement in the condition of a While loop?
View 12 Replies
View Related
Sep 29, 2015
How do I get the below scenario:
EmpID DepID
12 2
17 3
17 2
13 3
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
Desired Output:
EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2
View 5 Replies
View Related
May 28, 2015
I have customers named Alex (Cid=1), Bob (Cid=2), and Carrie (Cid=3) in a table customer.
Cid
First_Name
1
Alex
2
Bob
3
Carrie
I have products name Gin (Pid=1), Scotch (Pid=2) and Vodka (Pid=3) in a table products.
Pid
Product_Name
1
Gin
2
Scotch
3
Vodka
And I have a table that holds purchase called Customer_Purchases that contain the following records:
Cid
Pid
1
1
1
2
2
1
2
3
3
2
I would like to make a marketing list for all customers that purchased Gin or Scotch but exclude customers that purchased Vodka. The result I am looking for would return only 2 records: Cid’s 1 (Alex) and 3 (Carrie) but not 2 (because Bob bought Vodka).
I know how to make a SELECT DISTINCT statement but as soon as I include Pid=2 This clearly doesn’t work :
SELECT DISTINCT Pid, Cid
FROM
Customer_Purchases
WHERE (Cid = 1) OR
(Cid = 3) OR
(Cid <> 2)
View 3 Replies
View Related
Oct 1, 2015
I have a query that returns the data about test cases. Each test case can have multiple bugs associated to it. I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
TestCaseID TestCaseDescription BugID BugStatus
1 TestCase1 1 Closed
2 TestCase1 2 Open
3 TestCase2 11 Closed
4 TestCase2 12 Closed
5 TestCase2 13 Closed
How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.
View 3 Replies
View Related
Apr 30, 2015
What is wrong in this query am getting incorrect syntax near ','.error. I am using sql server 2014
DELETE ab, b
FROM Authors AS a INNER JOIN AuthorArticle AS ab ON a.AuthID=ab.AuthID
INNER JOIN Articles AS b ON ab.ArticleID=b.ArticleID
WHERE AuthorLastName='Tom';
View 4 Replies
View Related
Aug 15, 2015
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN
[code] ....
View 9 Replies
View Related
May 6, 2015
I am using SQL Server 2012 Standard Edition. I have a requirement to instantaneously move data from 3 tables that are dependent on each other in a database to another database to same tables with same structure and dependencies.
I can set up replication to manage this. However the data once moved over has to be deleted from the source database immediately after the move. Hence replication is ruled out. Also data is continuously being insert into source database into those 3 tables.
I want to create a sql agent job that handles the data move and delete process and shcedule it to run once every minute. What is a best strategy to handle this without causing deadlocks in the source database?Below is the ddl and all objects in source database match the destination database the only difference is destination has 100 tables and source has only 3 tables which are shown below
CREATE TABLE [dbo].[StackPosition](
[StackPositionId] [uniqueidentifier] NOT NULL,
[AccountTriggerId] [uniqueidentifier] NOT NULL,
[StackPositionStatusId] [int] NOT NULL,
[QueuedAt] [datetime] NOT NULL,
[LastUpdatedAt] [datetime] NULL,
[Code] ....
View 3 Replies
View Related
May 5, 2015
I have a table named [New Item] and created a default constraint on a column, and i wanted to change the data type of the column using the query
alter table [new item]
alter column pcs_qty decimal(15,2) not null
but the name of the default constraint is 'DF__New Item__Pcs_Qt__2D12A970' and i am not able to delete the constraint because it contains a space in between.Is there any work around for this.
I tried to delete the constraint by using the query
alter table [new item]
drop constraint 'DF__New Item__Pcs_Qt__2D12A970'
but I am getting the exception,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'DF__New Item__Pcs_Qt__2D12A970'.
View 2 Replies
View Related
Jul 21, 2015
I have a table with more than 1000 records. The columns look like this
Import id | Reference | Date | Username | Filename
Some of the rows are duplicates except for the fact that they have different values for the first column alone (import id)
11111 test 21-07-2015 xxxxxx yyyyyy
22222 test 21-07-2015 xxxxxx yyyyyy
Is it possible to delete these kind of duplicate rows by ignoring the import id ?
View 9 Replies
View Related
Jul 24, 2015
I'm trying to delete the selected data from a table colum from a select statement.
This is the select statement
SELECT RFC822 FROM SQLGOLDMINE.DBO.MAILBOX MB WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24') OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification
& invitation%')
What the delete statement would look like. I've tried replacing Select with delete from but get a sytax error.
View 9 Replies
View Related
Jun 25, 2015
We are not allowed to write delete statement in a function. It will give the below error.
"Invalid use of a side-effecting operator 'DELETE' within a function."
We will get the same error for truncate/drop statements.
What is the reason behind this error?
View 8 Replies
View Related
Jun 15, 2015
I am working on an app that getting quite a few deadlocks due to delete statements. I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks. I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.
What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap.
Open this url in new tab [URL] ....
View 7 Replies
View Related