Deleting rows with OPENXML

Hi there,
I'm trying to delete some rows from a table using an xml document (in
memory) and OpenXML in SQL Server. I have the following code, but it will
only delete the first record. Any ideas?
CREATE PROCEDURE [dbo]. [CompanyBusinessUnit_DeleteCompanyBusine
ssUnit_Delete]
@.CompanyHeirarchy nvarchar(4000)
AS
SET NOCOUNT ON;
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.CompanyHeirarchy
DELETE FROM CompanyBusinessUnit
WHERE CompanyBusinessUnitID IN (Select CompanyBusinessUnitID
FROM OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2) WITH
(CompanyBusinessUnitID Integer))
EXEC sp_xml_removedocument @.hDoc
GO
Thanks,
WesTry,
DELETE
CompanyBusinessUnit
FROM
CompanyBusinessUnit
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:

> Hi there,
> I'm trying to delete some rows from a table using an xml document (in
> memory) and OpenXML in SQL Server. I have the following code, but it will
> only delete the first record. Any ideas?
> CREATE PROCEDURE [dbo]. [CompanyBusinessUnit_DeleteCompanyBusine
ssUnit_Delete]
> @.CompanyHeirarchy nvarchar(4000)
> AS
> SET NOCOUNT ON;
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.CompanyHeirarchy
> DELETE FROM CompanyBusinessUnit
> WHERE CompanyBusinessUnitID IN (Select CompanyBusinessUnitID
> FROM OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2) WITH
> (CompanyBusinessUnitID Integer))
> EXEC sp_xml_removedocument @.hDoc
> GO
>
> Thanks,
> Wes|||Hi there,
Thanks for answering my post so quickly. Unfortunately your solution did
the same thing as the ones I've tried. It simply deleted the first row
matched from the XML. I have posted my XML below, maybe there is something
wrong with it.
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
Thanks,
Wes
"Alejandro Mesa" wrote:
> Try,
> DELETE
> CompanyBusinessUnit
> FROM
> CompanyBusinessUnit
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
>
> "Wes" wrote:
>|||Try,
DELETE
a
FROM
CompanyBusinessUnit as a
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:
> Hi there,
> Thanks for answering my post so quickly. Unfortunately your solution did
> the same thing as the ones I've tried. It simply deleted the first row
> matched from the XML. I have posted my XML below, maybe there is somethin
g
> wrong with it.
> <NewDataSet>
> <DeletedNodes>
> <CompanyBusinessUnitID>17</CompanyBusinessUnitID>
> <CompanyBusinessUnitID>18</CompanyBusinessUnitID>
> </DeletedNodes>
> </NewDataSet>
> Thanks,
> Wes
> "Alejandro Mesa" wrote:
>|||I thought the problem was with the DELETE statement, but no, it was the
"SELECT ... FROM OPENXML ..." that was selecting just the first row. Can you
try changing this select by:
Select
cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
where
nodetype = 3
AMB
"Alejandro Mesa" wrote:
> Try,
> DELETE
> a
> FROM
> CompanyBusinessUnit as a
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
> "Wes" wrote:
>|||Your firts post will also work if you change the layout of the xml document
to:
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
</DeletedNodes>
<DeletedNodes>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
AMB
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Excellent! Thank you very much as this one works perfectly. Now I'll have
to analyze the code and see what I was doing differently.
Thanks for your help.
Wes
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>

Deleting records.

Anyone have ideas on how to delete records (see code below). I keep on getting the error message:

"The column prefix 'employee' does not match with a table name or alias name used in the query."

All I want to do is to remove the records in the EMPRATES table where the EMPLOYEEID and RATE are the same in the EMPLOYEE table. What am I missing?

delete emprates
where
emprates.employeeid = employee.employeeid
and emprates.rate=employee.ratedelete emprates from emprates
inner join employee on
emprates.employeeid = employee.employeeid
and emprates.rate=employee.rate|||delete from emprates
where exists
( select 1 from employee
where employeeid = emprates.employeeid
and rate = emprates.rate )|||I tried both syntax and they both perfomed what I needed. The first one had a lower execution cost though.

Thanks again.sql

Deleting from Stored Procedures

I am trying to write a stored procedure to delete something from a table but I keep getting sytax error.

This is the code I am using:

DELETE [tablename].[tablerow], [tablename].[tablerow]
FROM [tablename]
WHERE (((tablename.tablerow)="void"));

This works as an Access query but not as a stored procedure.DELETE
FROM [tablename]
WHERE tablename.tablerow)='void'
GO

Deleting files

Hello there
On every w by Enterprise manager i:
1. Detatch the database
2. delete the log file
3. reattaching it again
I would like to do this by code.
Is there a way to do that?Roy Goldhammer wrote:

> Hello there
> On every w by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
Yes. Don't. Regularly detaching and deleting the log in an operational
system is certainly foolish, unnecessary and/or dangerous.
Firstly, set the correct recovery model - that means "Simple Recovery"
if you don't require log backups. Secondly, ensure you implement the
right tran log backups if you do need them. Finally, set the log file
to the right size, turn off autogrow and LEAVE IT ALONE.
More info:
http://support.microsoft.com/?id=110139
http://support.microsoft.com/?id=317375
http://support.microsoft.com/?id=315512
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
David Portas
SQL Server MVP
--|||Hi
Why?
What you are doing is unsupported and very dangerous. In some cases, you
will not be able to re-attach the database again and kiss your DB goodbye.
Rather put your database into the appropriate recovery mode, and backup the
log if it is not in simple mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OnjYA8GCGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Hello there
> On every w by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
>|||I agree with David and Mike. Don't do this. It will bite you in the
backside.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OnjYA8GCGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Hello there
> On every w by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
>

Deleting Databases in SMO for C#

Hey all,

I was trying to write a C# function that would delete everything in the old database before doing anything new. The following is the code I tried to use for the deleting part. I tried "Killdatabase" but it doesn't seem to be working. Anyway, is there a way to reference the database in the server and send commands to it? The compiler gives me a error message saying that srv.Databases("Name") is wrong because I am using a property as a method.

p.s. Someone posted a similar question the other day and they solved it in visual basic by introducing a local variable. It didn't work for me on C#.

Many Thanks

Server srv = new Server(con);

srv.Databases("Name").Drop();

Database d = new Database(srv, "Name");

d.Create();

d.ExecuteNonQuery(command);

Ooops, I am sorry. I should have used [] instead of (). But I did have to use a local variable.

Thanks anyway.

deleting data from database

Edited by SomeNewKid. Please post code between<code> and</code> tags.

---

I have added a delete button to my datagrid, and put in what I think is the code to delete a member from the database at their Member ID, however when I have called a members details and the delete button is pressed, nothing happens!!??
Any ideas?
Here is the code:

<%@. Page Language="VB" %>
<script runat="server"
 ' Insert page code here
 '
 Function GetMember(ByVal iD As Integer) As System.Data.SqlClient.SqlDataReader
 Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'"
 Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "SELECT [oga].* FROM [oga] WHERE ([oga].[ID] = @.ID)"
 Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
 sqlCommand.Parameters.Add("@.ID", System.Data.SqlDbType.Int).Value = iD
 sqlConnection.Open
 Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
 Return dataReader
 End Function
 Sub dgMember_Delete(sender as Object, e as DataGridCommandEventArgs)
 dgMember.EditItemIndex = -1
 dgMember.DataSource = GetMember(memberID.Text)
 dgMember.DataBind()
 End Sub
 Sub btnView_Click(sender As Object, e As EventArgs)
 dgMember.DataSource = GetMember(memberID.Text)
 dgMember.DataBind()
 End Sub
 Sub dgMember_SelectedIndexChanged(sender As Object, e As EventArgs)
 End Sub
 Function DeleteMember(ByVal iD As Integer) As Integer
 Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'"
 Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "DELETE FROM [oga] WHERE ([oga].[ID] = @.ID)"
 Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
 sqlCommand.Parameters.Add("@.ID", System.Data.SqlDbType.Int).Value = iD
 Dim rowsAffected As Integer = 0
 sqlConnection.Open
 Try
 rowsAffected = sqlCommand.ExecuteNonQuery
 Finally
 sqlConnection.Close
 End Try
 Return rowsAffected
 End Function
</script>
<html>
<head>
</head>
<body id="dgMemberInfo">
 <font face="arial">
 <h1><img src="http://pics.10026.com/?src=thlogotop.gif" align="left" /> <img src="http://pics.10026.com/?src=thlogotop.gif" align="right" />
 <br />
 <center><font color="red">T</font>albot <font color="red">H</font>eath <font color="red">O</font>ld <font color="red">G</font>irls <font color="red">A</font>ssociation
 </center>
 </h1>
 <h2 align="center">Delete a Member
 </h2>
 <asp:HyperLink id="HyperLink1" runat="server" Width="94px" NavigateUrl="Default.aspx">Main Menu</asp:HyperLink>
 <hr />
 <br />
 <br />
 <br />
 <p align="center">
 </p>
 <form runat="server">
 <br />
 <div align="center">Please Enter Member ID :
 <asp:TextBox id="memberID" runat="server"></asp:TextBox>
 </div>
 <br />
 <br />
 <br />
 <br />
 <div align="center">
 <asp:Button id="btnView" onclick="btnView_Click" runat="server" Width="204px" Text="View Member Details"></asp:Button>
 </div>
 <br />
 <br />
 <p align="center">
 <asp:DataGrid id="dgMember" runat="server" BorderColor="Black" OnSelectedIndexChanged="dgMember_SelectedIndexChanged" AutoGenerateColumns="False" OnDeleteCommand="dgMember_Delete">
 <Columns>
 <asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="Delete"></asp:ButtonColumn>
 <asp:BoundColumn DataField="ID" HeaderText="ID">
 <HeaderStyle font-bold="True" horizontalalign="Center" verticalalign="Middle"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="Member No" HeaderText="Member No">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="Surname" HeaderText="Surname">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="Forenames" HeaderText="Forenames">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="Known as" HeaderText="Known As">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="Title" HeaderText="Title">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="Address" HeaderText="Address">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="Home Tel" HeaderText="Home Tel">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="Email" HeaderText="Email">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="DOB" HeaderText="DOB">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="StartDate" HeaderText="StartDate">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="LeaveDate" HeaderText="LeaveDate">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 <asp:BoundColumn DataField="clubsoc" HeaderText="Club/Society">
 <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
 </asp:BoundColumn>
 </Columns>
 </asp:DataGrid>
 </p>
 <p align="center">
 </p>
 <p align="left">
 <br />
 <br />
  
 </p>
 <!-- Insert content here -->
 </form>
 </font>
</body>
</html>

try this

Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'"

should be

Dim connectionString As String = "server=local; trusted_connection=true; Database=adp1SQL"

localhost is for webserver. local is for sql server.

hth|||dgMember_Delete is not calling the DeleteMember method:

You will need to add something like:

'cast selected item
dim id as integer = Convert.ToInt32(e.Item.Cells(1).Text)
'Call the function passing the selected id
DeleteMember(id);

sql

Deleting and Updating from Gridview

Hi,

I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @.TransactionIDAND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid) "

It gives the error that Quantityavailable is not a SET type?

Thanks if you can suggest a remedy!

Jon

jbear123:

UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid

That is not valid syntax for an UPDATE statement.

Try this

UPDATE itemsSET Quantityavailable = Quantityavailable + 1, numtaken = numtaken - 1WHERE ItemId = @.ItemId

|||

Thanks!

deleting all rows in table which references itself

Using SQL Server 2000.
The example code below shows a
table which references itself.
i.e. column "parent" references column "pk" :
==
create table table1 (pk int not null primary key, parent int null,
aname varchar(50) null)
alter table table1 add constraint fk_table1_table1
foreign key (parent) references table1 (pk)
go
insert table1 values (1, null, 'one')
insert table1 values (11, 1, 'one-one')
go
delete from table1 -- XX
==
Say I want to delete all rows from the table,
as at the line marked at "XX". Is the
SQL server processing of the delete
statement, such that the delete statement
will always succeed, or can I actually
get a constraint violation in the above example ?
I'm not seeing a constraint violation, but I'm not
sure if this is by luck, or by design.
TIA,
StephenStephen
You can get an error only if you will provide WHERE condition such as
delete from table1 where pk=1
If you can explain what are you trying to do , so it will be more easier to
came up with a solution.
"Stephen Ahn" <noaddress_at_noaddress.com> wrote in message
news:OLFHs3YNFHA.1732@.TK2MSFTNGP14.phx.gbl...
> Using SQL Server 2000.
> The example code below shows a
> table which references itself.
> i.e. column "parent" references column "pk" :
> ==
> create table table1 (pk int not null primary key, parent int null,
> aname varchar(50) null)
> alter table table1 add constraint fk_table1_table1
> foreign key (parent) references table1 (pk)
> go
> insert table1 values (1, null, 'one')
> insert table1 values (11, 1, 'one-one')
> go
> delete from table1 -- XX
> ==
>
> Say I want to delete all rows from the table,
> as at the line marked at "XX". Is the
> SQL server processing of the delete
> statement, such that the delete statement
> will always succeed, or can I actually
> get a constraint violation in the above example ?
> I'm not seeing a constraint violation, but I'm not
> sure if this is by luck, or by design.
>
> TIA,
> Stephen
>|||Uri,
What I was trying to work out was a safe way to delete all records in
tables which reference themselves, as in the example.
ie.
1) is it 100% safe to do : delete from table1,
("safe", as in : no constraint violation will ever be raised
in cases similar to the example, and all records will get
properly deleted). The table could also have many records
in reality.
OR
2) should I write a stored proc etc which deletes records
one by one, deleting child records first, then eventally
deleting the root nodes.
From your reply, it sounds like 1) is true.
Thanks,
Stephen
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvY6iHbNFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Stephen
> You can get an error only if you will provide WHERE condition such as
> delete from table1 where pk=1
> If you can explain what are you trying to do , so it will be more easier
> to
> came up with a solution.
>|||Stephen
Again, you are asking to different questions
Do you really want NO WHERE condition in your query?
I think the option 2 is right way to do that.
"Stephen Ahn" <noaddress_at_noaddress.com> wrote in message
news:euTJhZbNFHA.1732@.TK2MSFTNGP14.phx.gbl...
> Uri,
> What I was trying to work out was a safe way to delete all records in
> tables which reference themselves, as in the example.
> ie.
> 1) is it 100% safe to do : delete from table1,
> ("safe", as in : no constraint violation will ever be raised
> in cases similar to the example, and all records will get
> properly deleted). The table could also have many records
> in reality.
> OR
> 2) should I write a stored proc etc which deletes records
> one by one, deleting child records first, then eventally
> deleting the root nodes.
> From your reply, it sounds like 1) is true.
> Thanks,
> Stephen
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uvY6iHbNFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%230IbsOcNFHA.3772@.TK2MSFTNGP15.phx.gbl...
> Do you really want NO WHERE condition in your query?
Yes, this is a special case where we actually want to clean out the table
completely.
Thanks,
Stephen|||DELETE FROM Table1
is "safe" in the sense that it will not cause any violations of the
self-referencing foreign key. Constraints are validated against the
final result of a DML operation (except in the special case where you
use user-defined functions in a constraint and the function references
other rows in the same table).
David Portas
SQL Server MVP
--

Delete with a commit statement

I need to delete about 2 million records in a table in DB2 v7.x, If there a way to code an SQL that would do a commit every 100000 rows, so it would not blow out the transaction logs?
Any help would be appreciated.This is what would do the job for Oracle, i hope it might help you

delete from table
where rownum<100000;
commit;

delete table1 from openxml

Hi,
I know how to select using openxml.
Could you please let me have a sample code on how to delete records from a table based on what is inside an xml.
Thanks

Something like:
delete from table1
where
OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)

There are a couple of ways to approach this. One of the easiest ways (again easy but not necessarily the highest performance) is to use a JOIN.

Example :

delete from table1 FROM table1 JOIN
SELECT contractMonth, [date], mid, bizdays

OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)) example1 on table1."commonkey" = example1."commonkey"

|||

You can use OPENXML in FROM clause so do below:

delete from table1

where exists (

select *

from OPENXML(...)

with (...) as x

where x.contractMonth = table1.contractMonth

and x.[date] = table.[date]

...

)

delete table1 from openxml

Hi,
I know how to select using openxml.
Could you please let me have a sample code on how to delete records from a table based on what is inside an xml.
Thanks

Something like:
delete from table1
where
OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)

There are a couple of ways to approach this. One of the easiest ways (again easy but not necessarily the highest performance) is to use a JOIN.

Example :

delete from table1 FROM table1 JOIN
SELECT contractMonth, [date], mid, bizdays

OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)) example1 on table1."commonkey" = example1."commonkey"

|||

You can use OPENXML in FROM clause so do below:

delete from table1

where exists (

select *

from OPENXML(...)

with (...) as x

where x.contractMonth = table1.contractMonth

and x.[date] = table.[date]

...

)

delete sql not working.

hi

win 2k and xp
excel 2k
sqlserver version 7
the code below execute but when i query the table, the data is still in there. can anyone help?

Sub UPDATED_DELETE()

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
dim MyDate As Date

MyDate = Format(Date, "MM/DD/YYYY")

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.ConnectionString = "ODBC=SQL Server;DSN=LOGCALL_TABLE;UID=richard;APP=Microsoft Query;WSID=RICHARD;Trusted_Connection=Yes"
conn.ConnectionTimeout = 30
conn.Open

Set cmd.ActiveConnection = conn

cmd.CommandText = "DELETE FROM LOGCALL_TABLE WHERE LOGCALL_TABLE.OpenCall like 'X' AND LOGCALL_TABLE.StopTime like '" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.EndTime like '" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.ClientName like '" & Range("B" & CStr(ActiveCell.Row)).Value & "' AND LOGCALL_TABLE.Representative like '" & Range("C1").Value & "' and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute
conn.Close
End SubI think it has to do with the date and the way it is formated in my sql statement.

.......and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute

i had it formated this way before as required by ms access, but that does not work.

..... and LOGCALL_DB.DateOnCall = # " & Date & " #;"

any thoughts on how to format this baby?

thanks in advance.

Alex|||I think it has to do with the date and the way it is formated in my sql statement.

.......and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute

i had it formated this way before as required by ms access, but that does not work.

..... and LOGCALL_DB.DateOnCall = # " & Date & " #;"

any thoughts on how to format this baby?

thanks in advance.

Alex|||to begin with, LIKE should only be used with strings

what is DateOnCall? datetime or varchar?|||Be smart and create a stored procedure in you SQL Server database that accepts StopTime, EndTime, ClientName, Representative, and DateOnCall as parameters and deletes the records you want. Then just call the procedure with the values from your spreadsheet.

...and look up the syntax and usage of the LIKE operator too. I suspect it needs wildcards, or at the very least is inappropriate for Date values (as Rudy said).

But I really think the problem is in your methodology, not your syntax...

Delete recordsets with same Date and Line

Hi All!

I need help with a Statement!
I am working with an Access2000 DB.
I have the following Problem.

ChNrLinieDatum Code 39 Stckzahl BHL1 BHL2 BMRH
582-064L2.1008.03.2005 02:30:00FCAA14821701
582-064L2.1008.03.2005 02:30:00FCAA14871701
582-114L2.1208.03.2005 01:00:00FAC827501240
582-114L2.1208.03.2005 01:00:00FAC827441240
582-114L2.1208.03.2005 01:00:00FAC827501240
582-094L2.707.03.2005 19:45:00FAE74323481
582-094L2.707.03.2005 19:45:00FAE74489481
582-094L2.707.03.2005 19:45:00FAE74489481
581-294L2.807.03.2005 18:20:00FA8V2658221
581-294L2.807.03.2005 18:20:00FA8V2652221
581-294L2.807.03.2005 18:20:00FA8V2658221
582-114L2.1207.03.2005 17:45:00FAAR20721236
As you can see I have a few recordsets that are double. The Thing is, there
is an ID that makes them different.

I need a Statement that deletes the surplus records where 'Datum' and
'Linie' are identical to another record. 1 record has to remain of course.
I thought of something like this.

DELETE FROM tbAuswert
WHERE EXISTS(
SELECT *
FROM tbAuswert
WHERE (Linie AND Datum)
IN (
SELECT Linie AND Datum AS Suchkrit
FROM tbAuswert
GROUP BY Suchkrit
HAVING ((Count(Suchkrit)>1)
)
)

But I get an error:
You wanted to execute a Query that did not have the following expression
'Not Linie = 0 And Not Datum = 0' in its Aggregatefunction

Perhaps you ccan help me.

Thanks
Julia

--
Message posted via http://www.sqlmonster.comJulia Hrtfelder via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I need help with a Statement!
> I am working with an Access2000 DB.
>...
> I need a Statement that deletes the surplus records where 'Datum' and
> 'Linie' are identical to another record. 1 record has to remain of course.
> I thought of something like this.
> DELETE FROM tbAuswert
> WHERE EXISTS(
> SELECT *
> FROM tbAuswert
> WHERE (Linie AND Datum)
> IN (
> SELECT Linie AND Datum AS Suchkrit
> FROM tbAuswert
> GROUP BY Suchkrit
> HAVING ((Count(Suchkrit)>1)
> )
> )
> But I get an error:
> You wanted to execute a Query that did not have the following expression
> 'Not Linie = 0 And Not Datum = 0' in its Aggregatefunction
> Perhaps you ccan help me.

If you don't have a primary that uniquely identifies each row, then
this will be very difficult, as SQL is designed to operate only from the
data in the tables.

It's possible that you can add a column that gives you a unique ID. Had
you been using SQL Server, I could have showed you how. However, since
you are using Access, you are better off posting your question to a
forum for Access. There are considerable differences between the SQL in
SQL Server and Access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 17 May 2005 12:09:04 GMT, Julia Hrtfelder via SQLMonster.com
wrote:

>Hi All!
>I need help with a Statement!
>I am working with an Access2000 DB.
>I have the following Problem.
(snip)
>As you can see I have a few recordsets that are double. The Thing is, there
>is an ID that makes them different.
>I need a Statement that deletes the surplus records where 'Datum' and
>'Linie' are identical to another record. 1 record has to remain of course.

Hi Julia,

The following will work for SQL Server. I'm not sure about Access though
(it deviates from the standard in other ways than SQL Server does <g>),
so test it first, and repost in an Access group if this doesn't work.

Note: I assume that the ID column is called "ID" and that you want to
retain the row with the lowest ID value.

DELETE FROM tbAuswert
WHERE EXISTS
(SELECT *
FROM tbAuswert AS a2
WHERE a2.Datum = tbAuswert.Datum
AND a2.Linie = tbAuswert.Linie
AND a2.ID > tbAuswert.ID)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||That one really worked out!

Thank you so much. You are great!

Julia

--
Message posted via http://www.sqlmonster.com

Delete query that doesn't work ?

Hi!

Do you know why this query is ok on SQL 2005 and not on SQL ce ?

Code Snippet

DELETE ProfilesGroups

FROM ProfilesGroups pg

INNER JOIN Groups g

ON tpGroupID = g.gId

WHERE pg.tpProfileID = '7df60fae-a026-4a0b-878a-0dd7e5308b09'

AND g.gProfileID = '8a6859ce-9f99-4aaf-9ed6-1af66cd15894'

Thx for help ;-).

PlaTyPuS

You can only reference a single table in a delete statement on SQL CE - a SQL CE limitation :-)

DELETE Procedure. How to return a value?

Hello,

I created a DELETE Stored Procedure in SQL 2005.
When calling this procedure from my server code (VB.NET in my case) I
need to know if the record was deleted or not.

How should I do this?

Should I make the procedure to return True or False? If yes, how can I
do this?

My Stored Procedure is as follows (I think it is ok):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@.ContentName NVARCHAR(100),
@.ContentCulture NVARCHAR(5)

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @.ContentId UNIQUEIDENTIFIER;
SELECT @.ContentId = ContentId FROM dbo.Content WHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentId IS NOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCulture IS NULL
BEGIN

-- Delete all localized contents from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.Content
DELETE
FROM dbo.Content
WHERE ContentName = @.ContentName;

END
ELSE

-- Delete localized content from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

GO

Thanks,
Miguel

hi miguel.

below is the sp i modified. i've added an output parameter, which will be null if record(s) are deleted properly. so you can check in your code using output parameter.

SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@.ContentNameNVARCHAR(100),
@.ContentCultureNVARCHAR(5) ,
@.nFlag tinyint output

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @.ContentIdUNIQUEIDENTIFIER;
SELECT @.ContentId = ContentIdFROM dbo.ContentWHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentIdISNOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCultureISNULL
BEGIN

-- Delete all localized contents from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.Content
DELETE
FROM dbo.Content
WHERE ContentName = @.ContentName;

Select @.nFlag = ContentIDfrom dbo.ContentLocalized
WHERE ContentId = @.ContentId
if @.nFlagisnull
Begin
Select @.nFlag = 1FROM dbo.Content
WHERE ContentName = @.ContentName;
End

END
ELSE

BEGIN
-- Delete localized content from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

Select @.nFlage = ContentIDFROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

END

hope it helps.

regards,

satish.|||Rather than do SELECT's after the DELETE's checking for @.@.ROWCOUNT works faster since the number of records deleted is already stored in the function. You could just check if @.@.ROWCOUNT >0 and set your flag to 1 or 0 appropriately.|||

Hi Dinakar

miguel has usedSET NOCOUNT ON.

thanks,

satish.

|||

Hello,

I defined "Set NoCount On" because this is done by default in SQL Server 2005 Management Studio and because this is how I see in most code examples.

I am just starting with SQL. Should I change it?

Could somebody give me some info on this?

Thanks,

Miguel

|||

hi miguel,

sorry i m not aware of default in sql 2k5

set nocount on --in case you update/insert/delete record on a table it wont return number of affected records

set nocount off --in case you update/insert/delete record on a table it will return number of affected records

hope it clarifies

now if you set it to off in your sp then after delete you can check

if @.@.ROWCOUNT > 0

begin

end

@.@.ROWCOUNT contains no.of records affected due to preceeding operation.

regards,

satish.

|||

SET NOCOUNT will only suppress messages from being printed in the Messages tab which are returned to the calling application. It does not have any effect on @.@.ROWCOUNT. You can still do your T-SQL Queries and have the results of rows affected in @.@.ROWCOUNT, get the value into variable and return it via OUTPUT parameters. Having the SET NOCOUNT ON is a good thing, generally. It prevents unnecessary messages from being passed across to the application.

Satish,

You are probably checking the Messages tab by running a T-SQL statement between SET NOCOUNT commands.

Try this:

SET NOCOUNT ON

SELECT TOP 10 * FROM anyTable

SELECT @.@.ROWCOUNT as RowsAffected

SET NOCOUNT OFF

|||

errrrr silly of me.

thanks dinakar you removed my misunderstanding about @.@.ROWCOUNT and SET NOCOUNT

cheers,

satish.