Transact SQL :: Deleting Records Based On Certain Logic
May 7, 2015
There are two seperate jobs,Job A and Job B, which run and insert records in a table. Job A runs first and then Job B runs. The task is to overwrite Job B records if Job A and Job B have same
PID,EDate,CP,RelativePNum,XrefCode,CPD
PID,EDate,CP,RelativePNum,XrefCode,CPD
In the above screenshot, row 1 and row 2 have same
So i want to write a query which deletes row 2 records, and keep row 1,row 3 and row 4 records. This i want to do in the whole table.
create
table AB
(
PID
int,
tTimeStamp
datetime,
EDate
varchar(40),
[Code] ....
View 12 Replies
ADVERTISEMENT
Mar 27, 2008
I am having a table where i have the following columns where the date format is dd/mm/yyyy
Purchase Description From_Date To_Date------------------------------- --------------- ----------------Desktop 2/2/2007 2/3/2007Mouse 2/1/2007 28/1/2007Laptop 5/1/2008 15/3/2008Speaker 4/1/2008 21/1/2008
My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?
Please provide me with full stored procedure
Thanx in advance
View 4 Replies
View Related
Mar 27, 2008
I am having a table where i have the following columns where the date format is dd/mm/yyyy
Purchase DescriptionFrom_DateTo_Date
------------------------- --------
Desktop2/2/20072/3/2007
Mouse2/1/200728/1/2007
Laptop5/1/200815/3/2008
Speaker4/1/200821/1/2008
My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?
Please provide me with full stored procedure
Thanx in advance
C.R.P RAJAN
View 1 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
Nov 6, 2015
I have 2 tables A, B with 1 to many relationship
Table A(ProductID), TableB(ProductID, FileID)
I need to find only the records in Table A that may have more than one FileIDs in Table B, since some ProductIDS have multiple FileIDs in Table B...
View 8 Replies
View Related
Aug 21, 2015
I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1
We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID
My tables look like
Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int)
Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)
Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)
select * from @tab1
1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only
2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column
3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column
My expected result set should be like ....
View 10 Replies
View Related
Oct 12, 2015
I've been able to get this select query to work, but I'm not sure how to modify it to turn it into a DELETE query:
USE QSCTestENG
select p.[testid], COUNT(c.[testid])
FROM [dbo].[tblTestHeader] p
left outer join [dbo].[tblTestMeasurements] c ON p.[testid]=c.[testid]
where p.[model] = 'XPPowerCLC125US12'
group by p.[testid]
having COUNT(c.[testid]) <>48;
View 2 Replies
View Related
Jul 28, 2015
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
View 4 Replies
View Related
Jul 2, 2015
I have a scenario to fetch records for each ID on 2 conditions. There are 2 types of Product type for each ID. PFE and PRI. I need only latest active PFE and at the same time all the latest PRI should be closed. (meaning, only PFE should be in Active status currently)
1.) Latest Product type PFE should be A (active) status for the particular ID
2.) At the same time ALL the latest PRI should be C(closed) status for the same ID
I have give example with 3 scenarios and desired output
1.) For ID 101, Latest PFE is active and all latest PRI is closed ----> Should come in result
2.) For ID 102, Latest PFE is Closed and all latest PRI is closed ---->Should NOT come in result
View 5 Replies
View Related
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Nov 27, 2007
every few days i'm getting an error in the application log , eventID 824, saying 'SQL Server detected a logical consistency-based i/o error; incorrect checksum. it then recommends completing a full database consistency check (dbcc checkdb).
i have run the dbcc checkdb ('databasename') and it is returning an error msg of:
Msg 8697, Level 16, State 215, Line 1
An internal error occurred in DBCC which prevented further processing
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors
i have oodles of disk space left. any ideas on where to go from here.
thank you for any help,
-Mark
View 3 Replies
View Related
Nov 13, 2014
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
[code]....
View 9 Replies
View Related
May 27, 2015
ALTER TABLE [dbo].[bkrm_lendcoll] ADD CONSTRAINT
ReqdCovgAmt_constraint33 CHECK
( case
when CovgAmt = 0 and ReqdCovgAmt = 0 then 1
when CovgAmt = 0 and ReqdCovgAmt = 1 then 1
when CovgAmt = 1 and ReqdCovgAmt = 0 then 1
when CovgAmt = 1 and ReqdCovgAmt = 1 then 0
end =1
)
This is my first attempt to add a constraint for business logic. The desired behavior is that the two columns together have the same behavior as a radio button. (one can be true, the other true, both can be false, but both cannot be true.) I get this error when I attempt to update it.
The ALTER TABLE statement conflicted with the CHECK constraint "ReqdCovgAmt_constraint33". The conflict occurred in database "Std", table "dbo.lendcoll".
So, basically my question is, when you have two bit columns and want them to have the truth table such as described, how can I set a Check constraint to enforce this?
View 14 Replies
View Related
Apr 20, 2015
In order to feed a fact table of a dwh from a staging table I'm using the MERGE statement in order to control insert and update operations. It is possible that the staging table has duplicate rows respect to the fields controlled in the merge condition:
When I run the first time the MERGE statement unwanted rows could be inserted in the fact table.
Does the MERGE statement allow to manage this case or do I need to filter data from the staging table before to write them into the fact table?
View 4 Replies
View Related
Apr 15, 2004
I have a table that contains rows that I would like to delete based on a field and it's contents.
What is the correct syntax to script the removal of these rows based field parameter?
View 7 Replies
View Related
Apr 12, 2007
Is it possible to delete multiple rows from multiple tables based on information specified. Can you write a query that would pull the information if you knew what tables it would need to look in? If anyone know I would greatly appreciate any help I am not sure of this.
Thanks,
Traci
View 1 Replies
View Related
Jun 15, 2015
I am developing for a customer and they want a search facility that uses boolean logic and special characters. So they want to be able to add "AND" "OR" "NOT" "*" and "?". And for this to effect the search in the predicted way and ranked. I was wondering if there is any examples of this type of search implemented?
View 3 Replies
View Related
Jul 16, 2015
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue
AS
(
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
return @ConcatenatedString
)
View 3 Replies
View Related
Jul 12, 2004
I must admit I dont know all that much about SQL, which is why I hope someone can show me the light. I have a script almost finished, however I have no idea how to have it trim database entries that are older than, say, 90 days. Any ideas?
View 10 Replies
View Related
Feb 25, 2005
I have a table with a load of orphaned records (I know... poor design)
I'm trying to get rid of them, but I'm having a brain cramp.
I need to delete all the records from the table "Floor_Stock" that
would be returned by this select statement:
SELECT FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE
FROM PRODUCT_MASTER INNER JOIN
FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT =
FLOOR_STOCK.PRODUCT LEFT OUTER JOIN
BOD_HEADER ON FLOOR_STOCK.PRODUCT =
BOD_HEADER.PRODUCT AND FLOOR_STOCK.SITE =
BOD_HEADER.SITE
WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
(PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))
I was thinking along the lines of:
DELETE FROM FLOOR_STOCK INNER JOIN
(SELECT FLOOR_STOCK. PRODUCT, FLOOR_STOCK.SITE
FROM PRODUCT_MASTER INNER JOIN
FLOOR_STOCK ON PRODUCT_MASTER. PRODUCT =
FLOOR_STOCK.PRODUCT
LEFT OUTER JOIN BOD_HEADER ON FLOOR_STOCK. PRODUCT =
BOD_HEADER. PRODUCT AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
(PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))) F ON
FLOOR_STOCK. PRODUCT = F. PRODUCT
AND FLOOR_STOCK.SITE = F.SITE
... but Sql Server just laughs at me: "Incorrect Syntax near the keyword INNER"
View 14 Replies
View Related
Mar 3, 2004
How do I delete one record from one table and cascade down all related tables?
Mike B
View 14 Replies
View Related
Aug 14, 2006
Hey all,
Here is the scenario. I'm working with two tables:
Contact1
Conthist
Contact1 contains basic contact information and conthist contains history records for those contacts. Conthist can hold many records related to a single contact1 record.
The link between the two tables is a column called accountno.
I'm trying to delete any records in conthist that have an accountno that does not exist in contact1. The queries that I've tried keep returning conthist records that do actually have a matching accountno.
Any help would be appreciated.
Thanks,
Tony
View 4 Replies
View Related
Apr 12, 2007
Whenever I try to delete records from a table joined to another table, like so:
DELETE [tblTransaction Detail Local] FROM [tblTransaction Detail Local], [tblTransaction Header] WHERE ([tblTransaction Detail Local].[Transaction ID] = [tblTransaction Header].[Transaction ID]) AND ([tblTransaction Header].[Computer ID] = 3)
I get the error:
Major Error 0x80040E14, Minor Error 25501
> DELETE [tblTransaction Detail Local] FROM [tblTransaction Detail Local], [tblTransaction Header] WHERE ([tblTransaction Detail Local].[Transaction ID] = [tblTransaction Header].[Transaction ID]) AND ([tblTransaction Header].[Computer ID] = 3)
There was an error parsing the query. [ Token line number = 1,Token line offset = 38,Token in error = FROM ]
This is an SQL CE database, and SQL Server Management Studio. Any ideas?
View 1 Replies
View Related
May 18, 2006
I have a couple SQL tables that have been appended to daily over the last two years. There is now about 50,000,000 records in the table. Does anyone know the fastest way to delete records before a certain date to shorten these tables? Delete queries and everything else I've tried is taking way too long.
View 13 Replies
View Related
May 21, 2004
Apparently, deleting 7,000,000 records from a table of about 20,000,000 is not advisable. We were able to take orders at 8:00AM, but not at 7:59.
So, what's the best way of going about deleting a large number of records? Pretty basic lookup table, no relationships with other tables, 12 or so address-type fields, 4 or 5 simple indexes. I can take it down for a weekend or night, if needed.
DTS the ones to keep to another table, drop the old and rename the new table?
Bulk copy out, truncate and bring back in?
DTS to text, truncate and import back?
Other ways?
Never worked with such a large table and need a little experienced guidance.
Thanks for the help
View 1 Replies
View Related
Jan 5, 2002
My Web Host does not provide administrative privilages to the SQL server I have access to. I would like to delete tens of thousands of records from two of my tables without writing to the Transaction Log. Is what I'm trying to do is delete these records quickly without utilizing any of the alotted space my web host has set aside for my transaction log (they give me 50 mb and I go way over that when I run a DELETE statement)
What is the best way to do this?
View 3 Replies
View Related
Jul 8, 1999
I need a sql statement to delete duplicate records.
I have a college table with all colleges in the nation.
I noticed that all of the colleges were listed twice.
How do I delete all of the duplicate records.
Here is my table.
Colleges
-------------------
schoolID - smallint NOT NULL,
schoolName - varchar(60) NULL
Can someone help me out with the sql statement???
I'm running SQL Server 6.5.
- ted
View 3 Replies
View Related
Aug 27, 2004
Hi All,
I am having one table named MyTable and this table contains only one column MyCol. Now i m having 10 records in it and all the records are duplicate ie value is 7 for all 10 records.
It is something like this,
MyCol
7
7
7
7
7
7
7
7
7
7
Now i m trying to delete 10th record or any record then it gives me error
"Key column information is insufficient or incorrect. Too many rows were affected by update."
What should i do if i want only 4 records insted 10 records in my table?
How do i delete the 6 records from table?
Plz help me.
Regards,
Shailesh
View 3 Replies
View Related
Jun 3, 2008
I have a problem where records in underlying tables of a dataview are being deleted (seemingly at random)
For example.
CREATE TABLE [Employee] (Id int, Name varchar(50))
CREATE TABLE [Company] (Id int, Name varchar(50))
CREATE TABLE [EmployeeCompany] (CompanyId int, EmployeeId int)
CREATE VIEW [dvEmployee]
AS
SELECT *
FROM [Employee] INNER JOIN [EmployeeCompany]
ON [Employee].[Id] = [EmployeeCompany].[EmployeeId]
CREATE TRIGGER [dvEmployeeUpdate]
ON [dbo].[dvEmployee]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE EmployeeCompany
SET Status = INSERTED.Status
FROM EmployeeCompany, INSERTED
WHERE EmployeeCompany.CompanyId = INSERTED.CompanyId
AND EmployeeCompany.EmployeeId = INSERTED.EmployeeId
END
Because the column [Status] is a t-sql keyword, does the fact that the trigger contains the line "SET Status = ..." without saying "SET [Status] = ..." mean that I could lose records in the EmployeeCompany table?
Reason I'm asking is we have an already designed database that is littered with columns named the same as sql keywords (almost every table has a [Status] column, and there are many [Password] columns). When using a dataview on these tables, triggers exist that aren't putting the [] around these column names (the same as my dvEmployeeUpdate trigger above), and somehow we are seemingly randomly losing records. It is very rare, and they are getting completely deleted, and it seems to be the tables that contain the keyword columns and are used in dataviews with instead of triggers that don't put [] around the column names. Nowhere in any trigger or stored procedure is there a DELETE FROM on these tables, and the software running on the database uses only the data views, and doesn't directly access the underlying tables.
I've been going through all of the code adding the [], but my question is simply whether or not anyone has heard of this causing the deletion of any records, or whether there may be something else going on that I should be looking into?
View 2 Replies
View Related
Jan 15, 2006
help me out on this one.
i have 2 text boxes in my page.
user enter any number in those two text boxes.
i slect that many record randomly from my main table, and put it into two another tables.
now the problem is coming in how to delete those records which were randomly selected from main table in main table.
for eg
main table contains
srNo. UswerID
1 abcd
2 trtr
3 tret
4 yghg
5 jjhj
user enters in text box1 '2' and in text box2 `1'
so total of 3 random records are selected
and put it into two another table say
table1
sr.no UserID
2 trtr
and table2 contains
sr.no. userid
3 tret
5 jjhj
now i want to delete these records which are sr.no 2,3,5 from the main table.
how do i do it as user can enter any number in the text box.so writing multiple delete statements would not be possible.
how do i write statements or help me with logic.
View 13 Replies
View Related
Dec 1, 2006
gaurav writes "respected sir
here i have a question
how we can delete duplicate records through query in SQL Server
thanks"
View 2 Replies
View Related
Oct 17, 2007
Hi
I wanted to do a delete rows from a group of table. These tables have a common column UserID. I heard that there is something called ondelete cascade. But I don't know how to set it up and utilise it. Could someone tell me how to do it. Or point me to a tutorial which shows how to do it.
Thanks
View 4 Replies
View Related
