How To Delete Records With A Field Containing NULL
Feb 9, 2008
Hi,
All records of a table must be deleted if a field (field1) contains NULL or is made empty by a previous update command.
I did this but it only works when the field is made empty, not when it contains NULL:
dim field1 as string = ""
...
sql = "delete from mytable WHERE (field1=@field1)"
comd = New SqlCommand(sql, oConnection)
comd.Parameters.AddWithValue("@field1", field1)
comd.ExecuteNonQuery()
How can i do that?
Thanks
Tartuffe
View 4 Replies
ADVERTISEMENT
Feb 17, 2008
Is there a way that I can reset the key field to 1 when using DELETE to clear a table?
(Note: if there is a separate command that I could use after the DELETE, that is fine too.)
Thank you for your help with this,
-DJ
View 2 Replies
View Related
Nov 10, 2014
I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code:
create table #nullTest (
name varchar(50)
,revenue int)
INSERT INTO #nullTest
Values ('Tim',100)
,('Andrew', 50)
,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code:
SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
View 4 Replies
View Related
May 19, 2012
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies
View Related
Aug 6, 2007
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
View 7 Replies
View Related
Dec 30, 2003
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
View 6 Replies
View Related
May 27, 2008
I have datagrid whose data source is set to SqlDataSource. SqlDataSource's connection string is set dynamically at pageload. When i click on delete link in gridview it executes delete command but row doesnt get deleted from data source. When i run Sql server profiler i found the delete statement is getting executed but the parameter is always null. why?
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False"
Caption="Events emitted by the application." DataKeyNames="EventId" OnDataBound="GridView1_DataBound" Width="600px" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" OnRowDeleting="GridView1_RowDeleting" OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:BoundField DataField="EventId" HeaderText="EventId" SortExpression="EventId" />
<asp:BoundField DataField="EventTime" HeaderText="EventTime" SortExpression="EventTime" />
<asp:BoundField DataField="Message" HeaderText="Message" SortExpression="Message" />
<asp:CommandField SelectText="Vew Details" ShowDeleteButton="True" ShowSelectButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" DeleteCommand="DELETE FROM aspnet_WebEvent_Events WHERE (EventId = @EventId)"
SelectCommand="SELECT EventId,EventTime,Message FROM aspnet_WebEvent_Events WHERE (ApplicationVirtualPath = @ApplicationVirtualPath)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList2" Name="ApplicationVirtualPath" PropertyName="SelectedValue" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="EventId" Type="String"/>
</DeleteParameters>
</asp:SqlDataSource>
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Delete")
SqlDataSource2.Delete();
}
View 4 Replies
View Related
Jan 2, 2007
Hi ,
I have One table Inside the Table, One Column have null values .
how to delete the null values from the table .Please Help me.
Thanks.
View 6 Replies
View Related
May 12, 2007
I am generally a C programmer and have little experience with DB programming, so I apologize right from the start.
I have a table that allows a registration item with a verification item that will be NULL when the item is created. What I wish to do is delete these if the verification item is still NULL after a specified time (say 24 to 48 hours).
I would prefer to do this with some sort of trigger in one of two ways and any suggestions are much appreciated.
1. Have a stored procedure or function (not sure which is best to utilize) that runs every 2 days which checks the table for the NULL values and deletes any older than 48 hours.
2. Create a stored procedure or function when the registration is made that will check if the verification is still NULL for that particular record 24 hours later.
I would think the first would be the simplest, but am not certain.
Again, forgive my inexperience with DB programming and again I appreciate any advice given.
Thanks,
Steven Henley
View 4 Replies
View Related
Jan 31, 2007
Hello, its hard to explain, i have a table like this: --userpage_visitors-- id bigint owner nvarchar(20) visitor nvarchar(20) created datetime
Then i have some code like this: (@Visitor is send to the stored proc)DECLARE @lastuser nvarchar(20)SELECT TOP 1 @lastuser = visitor FROM userpage_visitors WHERE (owner = @UserName) ORDER BY created DESCIF (@lastuser <> @Visitor)BEGININSERT INTO userpage_visitors (owner, visitor, created) VALUES (@UserName, @Visitor, @Created)-- delete hereEND
Now after i have inserted the new visitor into the table, i need to clean the table... so each user should have maximum of 30 visitors, so if the user i inserted above is the 31st user then i need to delete the first user, so i always have 30 fresch visitors,, if they have less then 30 visitors then nothing should happen. The question is, how can i get the 31th post? in mysql you can say that you want post 30, 31, but in mssql you only have the TOP to select limited posts, any ideas?
Patrick
View 1 Replies
View Related
Mar 21, 2007
Hello, I have 3 tables with their columns as follows: + LabelsInDocs [LabelId] PK FK , [DocsId] PK FK + Labels [LabelId] PK , [LabelName] + Docs [DocId] PK , [DocUrl] I set Cascade Delete On so when I delete a Doc all records in LabelsInDocs will be deleted. However, when a Doc is deleted I want also to delete all records in Labels for the labels which do not have any Doc associated to it in LabelsInDocs. How can I do this? Thanks, Miguel
View 1 Replies
View Related
Jun 3, 2008
hello friends.
i have table1 in aspnetdb.mdf and i have picturefile of columname.. datatype of picturefile is vchar(50)
i want to delete my record automatically from my table1 after two weeks from inserting my record date..
i want to delete my picture file that located /pictures/tree.jpg (example).. pictures/tree.jpg was uploaded as picturefile on table1 by user before
how can i do this ?
cheers
View 5 Replies
View Related
Nov 29, 2003
Currently i writing a program to delete Records in the SQL database using VB.net language...
BUT i not sure whether i am right???
Pls provide me with the coding using a command object to delete records in SQL database...thank...however i tried the codes below but not working ...Pls help
Dim StrConnection As String = "workstation id=""ET-T15404-PC1"";integrated security=SSPI; etc
Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "Delete Seller.Admin FROMSeller WHERE Seller.no=tb.no"
Dim dbComm As New SqlCommand(strSQL, objConnection)
objConnection.Open()
dbComm.ExecuteNonQuery
objConnecion.Close()
View 1 Replies
View Related
Jun 29, 2005
Hi.
there are 300 records on the table.
the table has a field of "ID (autonumber)"
How can I delete records without latest 20 records?
View 2 Replies
View Related
Oct 9, 2006
There are 30 tables in my database and I need to delete all records within a date range. Could anyone help me?
View 4 Replies
View Related
Sep 20, 2005
Hello,I have a database called articlesI want to delete all articles from a subgroup e.g. DVDIf I am in the enterprisemanager what steps do I have to take to do thisthanks in advancePaul
View 5 Replies
View Related
Jul 20, 2005
I have some records that will not delete, whenever I run a deletestatement in the Query analyzer, it never completes the statement, andI am only deleting one record at a time. Can anyone tell me why arecord wouldn't delete?
View 1 Replies
View Related
Nov 8, 2006
Hi!
New on this
Is there a way on a sql sever where i can
delete som records one time every day
like if a field in my table is < getdate()
Alvin
View 6 Replies
View Related
Oct 15, 2007
Hello,
I have a column called Prod_Class in a table. The data type is char(1) and allow null. The records have 3 distinct values ('E', 'P' and null) for Prod_Class in this table . When I run Select * from table1 where Prod_Class <> 'E', I expect I get the records that value equals to 'P' or is null. However, I only got records that the value equals to 'P'. If I run "Select * from table where Prod_Class <>'E' and Prod_Class <> 'P'", I got nothing while I expect I get the records that value is null. I know if I run "Select * from table where Prod_Class is null", I can get the records that value is null, but I need to know why I can not get result using "<>" and how to fix it. I am using SQL server 2005.
Thanks in advance for your help!
View 7 Replies
View Related
Jan 26, 2006
I have a Sequence Container with an Exec SQL Task in it. I can't delete the task or the container. I can't disable them.
Here's how I got there:
I put a Dataflow Task and the Exec SQL Task in the container, then set a precedence constraint. Lots of complaints moving either of them. Managed to delete the Dataflow Task, but now can't do anything.
Any dieas?
Laurence
View 1 Replies
View Related
Nov 16, 2015
I need to look at all tables in a database that has a column name of GEO
Then look for all values in each table where the GEO value is NULL and delete each of the records found...
View 6 Replies
View Related
Apr 28, 2004
I have a user who has submitted a survey twice, and I have to delete the second entry.
But every user has a UserID and the data for the second submission has the same UserID (the only thing different is the fact she submitted data 5 seconds later)as this is one of the unique values used in many tables.
So I have deleted data in other tables but If I try to delete it from this particulat table I can't as I am really trying to delete that ID, so how can I get rid off that record?
View 6 Replies
View Related
Aug 30, 2007
Is there a way to delete all records in a SQL Server 2005 database? A function or a script?
View 8 Replies
View Related
Sep 8, 2007
I have a table, gdbdoc, that contains record-key pairs, linking records in another table. There is no significance in the order of the link: if records A and B are linked, then I don't care whether the link is A -> B or B -> A, and my normal query logic is SELECT ... Where DCIindiid = A ... union SELECT ... Where DCILinkid = A(DCIindiid = key1, DCILinkid = Key2)
The link-creation process normally checks whether there is already a link in either direction. Thus before creating a link A->B the logic checks to see whether either the A->B or B->A link record exists, and a new link is not created if the link already exists in either direction. However recently one of my processes bypassed the reverse-link check, and I've ended up with a few hundred cases where there is both an A->B link and a B->A link.
If I run a query: - select gd1.* from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
this displays all the records where one record links A -> B and there is also another record that links B -> A.
How do I write a query to delete ONE of the pair of duplicate records? I have two problems: -
Problem 1: Table gdbdoc is keyed on (DCIindiid, DCILinkid). Both guids are needed to create a unique key, and the table does not have a single key field. You can't write DELETE gdbdoc where DCIIndiid, DCILinkid IN select gd1.dciindiid, gd1.linkid from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
as the DELETE ... SELECT ... syntax only seems to support a single returned value.
Problem 2. If we solved problem 1, we would (I think) delete BOTH the A->B link and the B->A link , whereas I only want to delete one of these links.
Afterthought: Problem 2 seems easily solvable: add "Where gd1.DCIindiid < gd1.DCILinkid" to the DELETE ... statement. Although the concept of "<" doesn't really mean anything with a guid, this is accepted by SQL, and halves the number of records returned by the select. Obviously I don't care which of the two links (A->B or B->A) is deleted.
Regards, Robert Barnes
View 3 Replies
View Related
Jul 30, 2004
I use a tabel for storin log data from a mail server. I noticed that I'm getting duplicate records, is there a way to delete the socond and/or third entry so I dont have any duplicates?
I need this done in SP.
View 1 Replies
View Related
Jun 1, 2000
Hello,
I would like to create a job that runs ones a day to delete records in a table that are older than one day. Any ideas?
Thanks,
Nina Melhem
View 2 Replies
View Related
Oct 19, 2000
Sorry for the new thread.
I have a userprofile table. There are a lot of duplicate records in this table. e.g.
USERID-----LASTNAME---EMAILADDRESS----CREATEDATE
----------------------------------------------------------------------
1----------A-----------A@yahoo.com---------2000-09-05 16:07:00.000
2----------A-----------A@yahoo.com---------2000-09-10 16:07:00.000
3----------A-----------A@yahoo.com---------2000-09-15 16:07:00.000
Userid is auto number, lastname and emailaddress are PK.
I want to delete duplicate records. If lastname and emailaddress are the same, only keep a record which createdate is the most newest date. See above example I only want to the record which userid is 3. I have alreday created a code which I attached below. This code onle keep a record which userid is 1.
Anybody can help me to solve this problem? Thanks.
============== My current code ====================
delete from userprofile where userprofile.userid in
--list all rows that have duplicates
(select p.userid
from userprofile as p
where exists
(select lastname, emailaddress
from userprofile
where lastname = p.lastname and emailaddress = p.emailaddress
group by lastname, emailaddress
having count (userid)>1))
and userprofile.userid not in
--list on row from each set of duplicate
(select min(p.userid)
from userprofile as p
where exists
(select lastname, emailaddress
from userprofile
where lastname = p.lastname and emailaddress = p.emailaddress
group by lastname, emailaddress
having count (userid)>1)
group by lastname, emailaddress)
View 2 Replies
View Related
Jul 20, 2005
:confused: Urgent!!
Hi there. I use MS SQL server. I would like to separate the data from one table to two tables refer to two reference tables and the following conditions:
Let say these two reference tables are called:
Table A & Table B
Group A:
1. Same date in Table A & Table B
2. Same ID in Table A & Table B (ID is not unique)
3. Same name in Table A & Table B (Name is not unique)
Combine all of these three conditions for unique identifier.
I used the following SQL code to separate the required data that match the above conditions to the new table.
(Code)
select a.Project, a.Site, a.S_number, a.Field_ID, a.Method, a.Analyte, a.Result, a.Units,
a.Qualifier, a.Dilution_Factor, a.Reporting_limit, a.Recovery_, a.Matrix, a.CAS_Number, a.Sample_Date,
a.Received_Date, a.Prep_Date, a.Analysis_Date, a.Batch_ID, a.Data_Package_num_SDG, a.Lab_Sample_ID,
a.Lab into APPL_union_exist from APPL_union_update a, Before_01012004_report b
where a.Field_ID = b.[Field Sample] and a.Sample_Date = b.Collected and a.Analyte = b.Analyte
However, I don't know how to delete the data that copied to the new table in original table, or separate that to the new table. Wish someone can help me. Thanks a lot
View 1 Replies
View Related
Mar 29, 2007
Hi,
i don't know if this is the right forum to post to, but here i go:
I'm having a problem...
I need to delete several records having as criteria their pk.
The deletion is made using SqlParameter in the code and a stored procedure on server.
as far as i know, i can do this, either using a IN list:
- build and pass the list of id's from code to the sp:
delete from tbl where attachment.id in (1,2,3)
OR
- execute a simple delete several times.
So far i tried first option, but i get errors in converting the list to ints; that is what the sql engine says.
I prefer this option because i think is faster and requires less roundtrips to the sql server.
My questions are:
1. how do i build a sane IN list?
2. is it worth to use a delete in a loop? the records that must be deleted are 5 at most, per job.
1 job can have [0..5] attachments.
Thank you,
Daniel
View 2 Replies
View Related
Nov 1, 2004
I want to modify a current DTS job I have. It simply copies records from one table to another after a given date. The catch is I only have read priviliges on the source table. I currently delete all the records from the destination table but have to use Query Analyzer to do it and then run the current package that is just a simple select statement. The statement in the DTS job is something like:
SELECT *
FROM tablename
WHERE date_field > '2004-09-30'
I have been running this multiple times per month since every week or so I want to get more recent data to run a couple of reports. The source table is not ours, it is in a different department, so I can only read off of it. The destination server is ours so I have full priviliges there. I assume the fact that the source server allows me limited access that I cannot run a "delete from tablename" first even though it is on our table? Is there another way to set this up? Thanks again. You guys are the best.
ddave
View 4 Replies
View Related
Nov 1, 2006
I have a database in Simple mode from which I wish to delete several million rows of one table (not all rows so 'TRUNCATE TABLE' is no good), without making the transaction log file grow to several GB in size. Any ideas??? I'm almost at the point where I might create a new table and copy the records I want to keep instead...
Thanks, Simon.
View 2 Replies
View Related
May 7, 2008
Hello everyone,
I have a little dilemma. I have a table ALLTABLE that has duplicate records and I want to delete them. ALLTABLE has these columns with these values for example:
Policy Premium Class State Entity Number
ADC-WC-0010005-0 25476 63 31 1
ADC-WC-0010005-0 25476 63 31 2
ADC-WC-0010005-0 25476 63 31 3
ADC-WC-0010005-0 1457 63 29 4
ADC-WC-0010092-1 2322 63 37 1
ADC-WC-0010344-0 515 63 01 1
ADC-WC-0010344-0 515 63 01 2
As you can see there is some duplicates in the first 3 rows and the final 2 (the entity number is the only difference). I want the table to look like this:
Policy Premium Class State Entity Number
ADC-WC-0010005-0 25476 63 31 1
ADC-WC-0010005-0 1457 63 29 4
ADC-WC-0010092-1 2322 63 37 1
ADC-WC-0010344-0 515 63 01 1
Thank you so much for the help. It is really appreciated.
View 11 Replies
View Related
Jun 5, 2008
Hi Guys,
I have the following table
customerid customername
------------------------
1 AAA
1 AAA
2 BBB
2 BBB
2 BBB
3 CCC
3 CCC
Here, I need to delete duplicate records from the above table.
After deleting the duplicate records the table should be
like this:
customerid customername
------------------------
1 AAA
2 BBB
3 CCC
Can any one help me!!!!!!
Regards
js.reddy
View 3 Replies
View Related