i have create a paramenters and use in the sql, but after i deleting it from
the sql statement, i still need to enter the paramenter in the PREVIEW.
i have check and clear the parameters clicking the "..." button but when i
check the properties of a table and set the expression, the parameter appears
in the "parameter" column.
i dun know if it is a bug or not...but thats strange
thanks for helping in advancehi all,
mmm i have solved the problem by deleting the <reportparamenter> tag in the
*.rdl code.
dont know if this is the formal method.
anyway, just share with you all.
"Jasonymk" wrote:
> i have create a paramenters and use in the sql, but after i deleting it from
> the sql statement, i still need to enter the paramenter in the PREVIEW.
> i have check and clear the parameters clicking the "..." button but when i
> check the properties of a table and set the expression, the parameter appears
> in the "parameter" column.
> i dun know if it is a bug or not...but thats strange
> thanks for helping in advance
>|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_data_v1_72uk.asp
"Although report parameters are created automatically from query parameters,
you manage report parameters separately in the report layout view. Also, if
you change the name of a query parameter, or delete a query parameter, the
report parameter that corresponds to the query parameter is not
automatically changed or deleted. You can remove the report parameter by
using the Report Parameters dialog box."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_designer_v1_6h9v.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:0B216069-4C8A-4404-AD60-D290AA1A0A8D@.microsoft.com...
>i have create a paramenters and use in the sql, but after i deleting it
>from
> the sql statement, i still need to enter the paramenter in the PREVIEW.
> i have check and clear the parameters clicking the "..." button but when i
> check the properties of a table and set the expression, the parameter
> appears
> in the "parameter" column.
> i dun know if it is a bug or not...but thats strange
> thanks for helping in advance
>|||In report layout, report menu, report parameters. Behind the scenes RS
creates the report parameters but they are two different things: the query
parameter and report parameter.
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:0B216069-4C8A-4404-AD60-D290AA1A0A8D@.microsoft.com...
> i have create a paramenters and use in the sql, but after i deleting it
from
> the sql statement, i still need to enter the paramenter in the PREVIEW.
> i have check and clear the parameters clicking the "..." button but when i
> check the properties of a table and set the expression, the parameter
appears
> in the "parameter" column.
> i dun know if it is a bug or not...but thats strange
> thanks for helping in advance
>
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Deleting from sql question.
I have a data source which I created a custom statement for which I need to delete from. I created the folowing selecct statement
SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @.UserName)
I wrote the following DELETE statement
DELETE FROM SrcComputer WHERE (CompId = @.CompId)
I have tested it in SQL server management and it does what I want it to do, but I don't know how to use it in my code. I put it inside of the DELETE tab inside of my custome statement and added a delete button inside of the gridview which utializes my data source, but it's not working. I don't know how to set the CompId variable? I want the statement to delete the row that the user clicks on. Can anyone give me some advice?
You can set the delete parameter like in this sample:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SNo" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="SNo" HeaderText="SNo" ReadOnly="True" SortExpression="SNo" />
<asp:BoundField DataField="CQNo" HeaderText="CQNo" SortExpression="CQNo" />
<asp:BoundField DataField="WorkDate" HeaderText="WorkDate" SortExpression="WorkDate" />
<asp:BoundField DataField="Analysis" HeaderText="Analysis" SortExpression="Analysis" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MSDN_forumConnectionString %>"
DeleteCommand="DELETE FROM [effort] WHERE [SNo] = @.SNo"
SelectCommand="SELECT [SNo], [CQNo], [WorkDate], [Analysis] FROM [effort]"
UpdateCommand="UPDATE [effort] SET [CQNo] = @.CQNo, [WorkDate] = @.WorkDate, [Analysis] = @.Analysis WHERE [SNo] = @.SNo">
<DeleteParameters>
<asp:Parameter Name="SNo" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CQNo" Type="String" />
<asp:Parameter Name="WorkDate" Type="DateTime" />
<asp:Parameter Name="Analysis" Type="Decimal" />
<asp:Parameter Name="SNo" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
ok I believe that I have done what you said. Most of it was already there the Parameter CompId's type wasn't there but the rest was there here is what I have.
<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:srcConnectionString1 %>"SelectCommand="SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @.UserName)"OnSelecting="SqlDataSource2_Selecting"DeleteCommand="DELETE FROM SrcComputer WHERE [CompId] = @.CompId"><SelectParameters><asp:SessionParameterName="UserName"SessionField="UserName"Type="String"/></SelectParameters><DeleteParameters><asp:ParameterName="CompId"Type="int64"/></DeleteParameters></asp:SqlDataSource>My question is where do assign a value for CompId. When I click the delete link it does nothing. Just seems to refresh the page and nothing changes.
|||You need to set DataKeyNames="CompId" from your gridview .|||limno,
Thank you. That is exactly what I needed. I have one other question for you. If I am already using the DataKeyNames for a different variable is it possible to have 2? It just so happened in this case I could use the same variable for both selecting and deleting.
Thanks, Matt
|||Matt,
You can have more than one as your DataKeyNames, just separate them with comma like DataKeyNames="id1, id2".
Wednesday, March 7, 2012
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;
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 w/ foreign key question
Is there a way to see the locks associated with a delete statement on a table (tab1) that has 5 or 6 Foreign key relationships. Trying to understand the impact of the delete on concurrency There are several delete deadlocks on one of the foreign key tables (tab2) and the system does not delete from the table (tab2) that is throwing the delete deadlock error. Wondering about the impact of foreign keys on deletes on Tab1 on Tab2.
Didn't see anything in query analyzer that would show the actual locks. It seems to have scans or such but no lock levels etc are shown.
Does anyone have any knowledge of how to see the actual locks thrown by a given statement. The delete on Tab1 statement is very quick so using EM has proved fruitless.
MikeOh, such simple question but resolving blocking/deadlocking is really an art. You might want to start here.
http://support.microsoft.com/kb/224453
Didn't see anything in query analyzer that would show the actual locks. It seems to have scans or such but no lock levels etc are shown.
Does anyone have any knowledge of how to see the actual locks thrown by a given statement. The delete on Tab1 statement is very quick so using EM has proved fruitless.
MikeOh, such simple question but resolving blocking/deadlocking is really an art. You might want to start here.
http://support.microsoft.com/kb/224453
Delete Triggers/Procedures...
What's the SQL statement that will allow me to delete all the triggers and procedures that I have created. (I don't remember the names of the procedures and triggers, but would like to clear everything). Thanks.If you can connect to the schema which created those triggers & procedures, you can get the names by querying the user_objects views.
select object_type, object_name
from user_objects
where object_type like 'PROC%' or object_type like 'TRIG%';
And to delete all the above, issue these sqls:
sql> set feedback off
sql> set termout off
sql> spool c:\temp\droptrigproc.sql
sql> select 'drop '||object_type||' '||object_name
2> from user_objects
3> where object_type like 'PROC%' or object_type like 'TRIG%';
sql> spool off
sql> @.c:\temp\droptrigproc.sql
Before doing the above, make sure you are NOT deleting other required objects !
Or the fastest way to cleanup would be to drop the schema/user and recreate it.
Originally posted by VB_Oracle
What's the SQL statement that will allow me to delete all the triggers and procedures that I have created. (I don't remember the names of the procedures and triggers, but would like to clear everything). Thanks.
select object_type, object_name
from user_objects
where object_type like 'PROC%' or object_type like 'TRIG%';
And to delete all the above, issue these sqls:
sql> set feedback off
sql> set termout off
sql> spool c:\temp\droptrigproc.sql
sql> select 'drop '||object_type||' '||object_name
2> from user_objects
3> where object_type like 'PROC%' or object_type like 'TRIG%';
sql> spool off
sql> @.c:\temp\droptrigproc.sql
Before doing the above, make sure you are NOT deleting other required objects !
Or the fastest way to cleanup would be to drop the schema/user and recreate it.
Originally posted by VB_Oracle
What's the SQL statement that will allow me to delete all the triggers and procedures that I have created. (I don't remember the names of the procedures and triggers, but would like to clear everything). Thanks.
Saturday, February 25, 2012
Delete statement using a join plz help
well i have 2 table one name detcom and another entcom stored in DB1 the key for both to join on is lets say A, B, C . I need to check if there are records based on the key A, B, C of both table where C EQUALS to '80_300_113' and if there are delete them and then grab data from another
database named DB2 on same server (same instance) wich contains the same tables entcom and detcom and insert all the data from those tables into the same tables in DB1 based on the key and where C = '80_300_113'
PLZ helpDo I understand it right that you basically want to replace data in one DB with data from another database?
database named DB2 on same server (same instance) wich contains the same tables entcom and detcom and insert all the data from those tables into the same tables in DB1 based on the key and where C = '80_300_113'
PLZ helpDo I understand it right that you basically want to replace data in one DB with data from another database?
Delete statement trouble
I have an Oracle 8i database with a table called GM_INV_CST. This table has the following fields:
STORE_CD NOT NULL VARCHAR2(4)
SKU_NUM NOT NULL VARCHAR2(12)
PLUS_MINUS NOT NULL VARCHAR2(1)
RCV_DT NOT NULL DATE
UNIT_CST NOT NULL NUMBER(10,2)
QTY NOT NULL NUMBER(6)
CST_TOT NOT NULL NUMBER(13,2)
I'm using the following statement to identify duplicate entries of based on SKU_NUM, STORE_CD, and PLUS_MINUS, then identify the entries with zero (0) quantities in QTY and CST_TOT:
select base.cnt,base.sku_num,base.store_cd,gm_inv_cst.plu s_minus,
gm_inv_cst.rcv_dt,gm_inv_cst.unit_cst,gm_inv_cst.q ty,gm_inv_cst.cst_tot
from gm_inv_cst,
(select count(*)as cnt,sku_num,store_cd,plus_minus
from gm_inv_cst
group by sku_num,store_cd,plus_minus
having count(*)>1) base
where gm_inv_cst.cst_tot = 0
and gm_inv_cst.qty = 0
and gm_inv_cst.sku_num=base.sku_num
and gm_inv_cst.store_cd=base.store_cd
and base.plus_minus = gm_inv_cst.plus_minus
...the problem is I can't seem to find the right syntax to create a delete statement for these records, anyone?My first observation is that you might have better luck posting an Oracle question in the Oracle (http://www.dbforums.com/f4) forum. The folks that hang out in the engine specific forums can often suggest specific features of a given engine that make the job a lot simpler than doing it the way that is required by the SQL standard.
The next observation is that you haven't specified the primary key column(s). This would help a lot to determine the simplest answer to your question.
-PatP|||If you already identified duplicate records, can't you use this query to delete them? Minor modification would then be
DELETE FROM gm_inv_cst
WHERE (plus_minus, rcv_dt, unit_cst, qty, cst_tot) IN
(SELECT g.plus_minus, g.rcv_dt, g.unit_cst, g.qty, g.cst_tot
FROM gm_inv_cst g,
(SELECT COUNT(*) AS cnt, sku_num, store_cd, plus_minus
FROM gm_inv_cst
GROUP BY sku_num, store_cd, plus_minus
HAVING COUNT(*) > 1
) base
WHERE g.cst_tot = 0
AND g.qty = 0
AND g.sku_num = base.sku_num
AND g.store_cd = base.store_cd
AND g.plus_minus = base.plus_minus
);|||I think your statement is close however it returns well over 9000 records where my original select statement only returns 463?|||Your original consolidates the duplicates, there have to be at least twice as many rows in the target table as in your result set, and quite possibly more than that!
-PatP|||It is your data, after all ... if this statement is close, enhance it a little bit and it'll be just fine :)
STORE_CD NOT NULL VARCHAR2(4)
SKU_NUM NOT NULL VARCHAR2(12)
PLUS_MINUS NOT NULL VARCHAR2(1)
RCV_DT NOT NULL DATE
UNIT_CST NOT NULL NUMBER(10,2)
QTY NOT NULL NUMBER(6)
CST_TOT NOT NULL NUMBER(13,2)
I'm using the following statement to identify duplicate entries of based on SKU_NUM, STORE_CD, and PLUS_MINUS, then identify the entries with zero (0) quantities in QTY and CST_TOT:
select base.cnt,base.sku_num,base.store_cd,gm_inv_cst.plu s_minus,
gm_inv_cst.rcv_dt,gm_inv_cst.unit_cst,gm_inv_cst.q ty,gm_inv_cst.cst_tot
from gm_inv_cst,
(select count(*)as cnt,sku_num,store_cd,plus_minus
from gm_inv_cst
group by sku_num,store_cd,plus_minus
having count(*)>1) base
where gm_inv_cst.cst_tot = 0
and gm_inv_cst.qty = 0
and gm_inv_cst.sku_num=base.sku_num
and gm_inv_cst.store_cd=base.store_cd
and base.plus_minus = gm_inv_cst.plus_minus
...the problem is I can't seem to find the right syntax to create a delete statement for these records, anyone?My first observation is that you might have better luck posting an Oracle question in the Oracle (http://www.dbforums.com/f4) forum. The folks that hang out in the engine specific forums can often suggest specific features of a given engine that make the job a lot simpler than doing it the way that is required by the SQL standard.
The next observation is that you haven't specified the primary key column(s). This would help a lot to determine the simplest answer to your question.
-PatP|||If you already identified duplicate records, can't you use this query to delete them? Minor modification would then be
DELETE FROM gm_inv_cst
WHERE (plus_minus, rcv_dt, unit_cst, qty, cst_tot) IN
(SELECT g.plus_minus, g.rcv_dt, g.unit_cst, g.qty, g.cst_tot
FROM gm_inv_cst g,
(SELECT COUNT(*) AS cnt, sku_num, store_cd, plus_minus
FROM gm_inv_cst
GROUP BY sku_num, store_cd, plus_minus
HAVING COUNT(*) > 1
) base
WHERE g.cst_tot = 0
AND g.qty = 0
AND g.sku_num = base.sku_num
AND g.store_cd = base.store_cd
AND g.plus_minus = base.plus_minus
);|||I think your statement is close however it returns well over 9000 records where my original select statement only returns 463?|||Your original consolidates the duplicates, there have to be at least twice as many rows in the target table as in your result set, and quite possibly more than that!
-PatP|||It is your data, after all ... if this statement is close, enhance it a little bit and it'll be just fine :)
Friday, February 24, 2012
Delete statement times out and blocks reads
Hi. Periodically I need to run a delete statement that deletes old data. The problem is that this can timeout using ODBC (via the CDatabase and CRecordSet classes in legacy code). Also, while its running the delete, the table its operating on is locked and my application can't continue to run and operate on rows not affected by the delete.
Are there any workarounds for this? Can the timeout be set in the connect string?
Thanks,
Brian
No, you will have to set the timeout value on the appropiate command object.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Delete statement of one table is blocking all other users
Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users ??
regards
Hi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.
|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've added
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards
|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards
|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>[quoted text clipped - 14 lines]
|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...[vbcol=seagreen]
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:
|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.
|||In addition to the other posts: Are you saying that you have foreign key referring to this table? If
so, did you create indexes over those foreign key columns in the other tables?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users ??
> regards
|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:[vbcol=seagreen]
>In addition to the other posts: Are you saying that you have foreign key referring to this table? If
>so, did you create indexes over those foreign key columns in the other tables?
>[quoted text clipped - 6 lines]
|||So do you have indexes on your foreign key columns? This can help delete and update on the
referenced table a *lot*.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...[vbcol=seagreen]
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users ??
regards
Hi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.
|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've added
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards
|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards
|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>[quoted text clipped - 14 lines]
|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...[vbcol=seagreen]
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:
|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.
|||In addition to the other posts: Are you saying that you have foreign key referring to this table? If
so, did you create indexes over those foreign key columns in the other tables?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users ??
> regards
|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:[vbcol=seagreen]
>In addition to the other posts: Are you saying that you have foreign key referring to this table? If
>so, did you create indexes over those foreign key columns in the other tables?
>[quoted text clipped - 6 lines]
|||So do you have indexes on your foreign key columns? This can help delete and update on the
referenced table a *lot*.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...[vbcol=seagreen]
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:
Delete statement of one table is blocking all other users
Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users '?
regardsHi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've added
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>>Hi sarajuma !
>>You could use a more granualar lock like ROWLOCK specified after the
>>Tablename in the Delete Statement. This will produce more overhead but
>>could solve you problem in some ways.
>>DELETE FROM SomeTable WITH (ROWLOCK)
>>HTH, Jens Suessmeyer.
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>>Hi sarajuma !
>[quoted text clipped - 14 lines]
>> regards|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:
>>Hi
>>You have to have a WHERE condition in your DELETE statement . Also you can
>>remove all indexes defined on the table and re-create them after
>>deletion.
>>Hi sarajuma !
>>[quoted text clipped - 14 lines]
>> regards|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.|||In addition to the other posts: Are you saying that you have foreign key referring to this table? If
so, did you create indexes over those foreign key columns in the other tables?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users '?
> regards|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:
>In addition to the other posts: Are you saying that you have foreign key referring to this table? If
>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>[quoted text clipped - 6 lines]
>> regards|||So do you have indexes on your foreign key columns? This can help delete and update on the
referenced table a *lot*.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:
>>In addition to the other posts: Are you saying that you have foreign key referring to this table?
>>If
>>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>>[quoted text clipped - 6 lines]
>> regards|||are there any indexed views involved?|||On Tue, 13 Dec 2005 07:26:42 GMT, "sarajuma" <u16630@.uwe> wrote:
>I want to execute a delete statement for one table , which is very common
>and so many tables refers to it
>when the delete is hapening all the users will be blocked and the delete
>never happens
>Is there any way that I can delete rows without blocking other users '?
One would expect it to complete.
Can you show us the SQL for the delete, and/or the table declaration?
Are there any joins involved in the delete?
J.
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users '?
regardsHi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've added
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>>Hi sarajuma !
>>You could use a more granualar lock like ROWLOCK specified after the
>>Tablename in the Delete Statement. This will produce more overhead but
>>could solve you problem in some ways.
>>DELETE FROM SomeTable WITH (ROWLOCK)
>>HTH, Jens Suessmeyer.
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>>Hi sarajuma !
>[quoted text clipped - 14 lines]
>> regards|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:
>>Hi
>>You have to have a WHERE condition in your DELETE statement . Also you can
>>remove all indexes defined on the table and re-create them after
>>deletion.
>>Hi sarajuma !
>>[quoted text clipped - 14 lines]
>> regards|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.|||In addition to the other posts: Are you saying that you have foreign key referring to this table? If
so, did you create indexes over those foreign key columns in the other tables?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users '?
> regards|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:
>In addition to the other posts: Are you saying that you have foreign key referring to this table? If
>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>[quoted text clipped - 6 lines]
>> regards|||So do you have indexes on your foreign key columns? This can help delete and update on the
referenced table a *lot*.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:
>>In addition to the other posts: Are you saying that you have foreign key referring to this table?
>>If
>>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>>[quoted text clipped - 6 lines]
>> regards|||are there any indexed views involved?|||On Tue, 13 Dec 2005 07:26:42 GMT, "sarajuma" <u16630@.uwe> wrote:
>I want to execute a delete statement for one table , which is very common
>and so many tables refers to it
>when the delete is hapening all the users will be blocked and the delete
>never happens
>Is there any way that I can delete rows without blocking other users '?
One would expect it to complete.
Can you show us the SQL for the delete, and/or the table declaration?
Are there any joins involved in the delete?
J.
Delete statement of one table is blocking all other users
Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users '?
regardsHi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've adde
d
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>
>[quoted text clipped - 14 lines]|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...[vbcol=seagreen]
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.|||In addition to the other posts: Are you saying that you have foreign key ref
erring to this table? If
so, did you create indexes over those foreign key columns in the other table
s?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users '?
> regards|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:[vbcol=seagreen]
>In addition to the other posts: Are you saying that you have foreign key re
ferring to this table? If
>so, did you create indexes over those foreign key columns in the other tabl
es?
>
>[quoted text clipped - 6 lines]|||So do you have indexes on your foreign key columns? This can help delete and
update on the
referenced table a *lot*.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...[vbcol=seagreen]
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users '?
regardsHi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've adde
d
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>
>[quoted text clipped - 14 lines]|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...[vbcol=seagreen]
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.|||In addition to the other posts: Are you saying that you have foreign key ref
erring to this table? If
so, did you create indexes over those foreign key columns in the other table
s?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users '?
> regards|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:[vbcol=seagreen]
>In addition to the other posts: Are you saying that you have foreign key re
ferring to this table? If
>so, did you create indexes over those foreign key columns in the other tabl
es?
>
>[quoted text clipped - 6 lines]|||So do you have indexes on your foreign key columns? This can help delete and
update on the
referenced table a *lot*.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...[vbcol=seagreen]
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:
DELETE statement conflicted
Hello
I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys.
However, when I tried to delete a row in the first table I saw this error:
DELETE FROM [dbo].[Names_DB]
WHERE [LName_Name]=N'andrews'
Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'.
I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables.
Please advise.
I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers.
Thanks
Regards
Lynn
Hello Fredrik
Thanks for the swift reply.
No I have not enabled the Cascade Delete Related Record I didn't know this was required, I am still a novice. Yes I have certainly missed this.
Now that I understand that this is required, I need to learn how to do this. Can you please direct me to a suitable turorial or perhaps explain how this is carried out and what is the required code for this process?
Thanks
Lynn
|||When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.
Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)
|||
Fredrik N:
When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.
Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)
Hello Fredrik
Within table properties where do I enable the delete to the existing tables. Can I add update also?
Thanks
Lynn
|||Hi
You can add constrains to a table it could be something like this:
CREATE TABLE Books ( BookIDINTNOT NULLPRIMARY KEY, AuthorIDINTNOT NULL, BookNameVARCHAR(100)NOT NULL, PriceMONEYNOT NULL)GOCREATE TABLE Authors ( AuthorIDINTNOT NULLPRIMARY KEY,Name VARCHAR(100)NOT NULL)GOALTER TABLE BooksADD CONSTRAINT fk_authorFOREIGN KEY (AuthorID)REFERENCES Authors (AuthorID)
ON DELETE CASCADE
ON UPDATE CASCADEGOYou can take a look atDatabase Objects: Constraints for moreHope this helps.|||
Hi Thanks for the post and the info.
As I have already created tables I am a little wary about altering tables in case I loose the data.
The tables I have already have foreign keys and primary keys.
I have a parent table called: DomNames
Primary Key = DomNamesID
I have recently inserted a foreign key:
Foreign Key = CatA_ID (taken from the Catagory A child table)
Then I have child category tables from A - Z
In category table A
Primary Key = CatA-ID
Foreign Key = DomNamesID (taken from the DomNames table)
In category table B
Primary Key = CatB-ID
Foreign Key = CatA_ID (taken from the previous A category table)
Each following table uses the Category ID alphabetical letter as a primary key and the previous tables Category ID as the foreign key, each table has a DomNameID column.
All tables contain data.
Do I miss out the first section of the code you mentioned and just put this:
ALTER TABLE DomNames
ADD CONSTRAINT fk_CatA_ID
FOREIGN KEY (CatAID)
REFERENCES DomNames (DomNamesID)
ON DELETE CASCADE
ON UPDATE CASCADE
I would be grateful if you would confirm before I alter my database.
Thanks
Regards
Lynn
Delete statement again
set rowcount 100000
declare @.a int
while 1=1
begin
begin tran del1
delete from dbo.extt_vpm_ldr_stg_2
select @.a = @.@.rowcount
print @.a
commit tran del1
if @.a < 100000 break;
end
Hi
TOTAL TABLE SIZE - 650 000 records
I am using the above statement to delete in chunks on 100000.
After every 100000 I have put a COMMIT TRAN. Hence as per the logic it
should delete 100000 commit and delete the next 100000.
And the transaction log is suppose 100MB before the statement is
executed. It is increasing in steps of some 30MB for each delete to
upto some 250MB and then goes back to some 50MB.
What we need is the transaction log must increase for every delete and
drop and again increase and so on.
How can the above statement be modified to take care of this behaviour?
Please reply asap.
Thanks
HarishIs your databsae set to simple recovery mode? If it is set to full recovery
the transaction log will grow until you backup or truncate the log.
Note if you set to simple recovery mode you will not be able to use log
backups for log shipping or disaster recovery. Same thing if you truncate
the log (Backup Log <database name> with truncate_only).
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131552570.094908.5060@.z14g2000cwz.googlegroups.com...
> set rowcount 100000
> declare @.a int
> while 1=1
> begin
> begin tran del1
> delete from dbo.extt_vpm_ldr_stg_2
> select @.a = @.@.rowcount
> print @.a
> commit tran del1
> if @.a < 100000 break;
> end
>
> Hi
> TOTAL TABLE SIZE - 650 000 records
> I am using the above statement to delete in chunks on 100000.
> After every 100000 I have put a COMMIT TRAN. Hence as per the logic it
> should delete 100000 commit and delete the next 100000.
> And the transaction log is suppose 100MB before the statement is
> executed. It is increasing in steps of some 30MB for each delete to
> upto some 250MB and then goes back to some 50MB.
> What we need is the transaction log must increase for every delete and
> drop and again increase and so on.
> How can the above statement be modified to take care of this behaviour?
> Please reply asap.
> Thanks
> Harish
>
declare @.a int
while 1=1
begin
begin tran del1
delete from dbo.extt_vpm_ldr_stg_2
select @.a = @.@.rowcount
print @.a
commit tran del1
if @.a < 100000 break;
end
Hi
TOTAL TABLE SIZE - 650 000 records
I am using the above statement to delete in chunks on 100000.
After every 100000 I have put a COMMIT TRAN. Hence as per the logic it
should delete 100000 commit and delete the next 100000.
And the transaction log is suppose 100MB before the statement is
executed. It is increasing in steps of some 30MB for each delete to
upto some 250MB and then goes back to some 50MB.
What we need is the transaction log must increase for every delete and
drop and again increase and so on.
How can the above statement be modified to take care of this behaviour?
Please reply asap.
Thanks
HarishIs your databsae set to simple recovery mode? If it is set to full recovery
the transaction log will grow until you backup or truncate the log.
Note if you set to simple recovery mode you will not be able to use log
backups for log shipping or disaster recovery. Same thing if you truncate
the log (Backup Log <database name> with truncate_only).
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131552570.094908.5060@.z14g2000cwz.googlegroups.com...
> set rowcount 100000
> declare @.a int
> while 1=1
> begin
> begin tran del1
> delete from dbo.extt_vpm_ldr_stg_2
> select @.a = @.@.rowcount
> print @.a
> commit tran del1
> if @.a < 100000 break;
> end
>
> Hi
> TOTAL TABLE SIZE - 650 000 records
> I am using the above statement to delete in chunks on 100000.
> After every 100000 I have put a COMMIT TRAN. Hence as per the logic it
> should delete 100000 commit and delete the next 100000.
> And the transaction log is suppose 100MB before the statement is
> executed. It is increasing in steps of some 30MB for each delete to
> upto some 250MB and then goes back to some 50MB.
> What we need is the transaction log must increase for every delete and
> drop and again increase and so on.
> How can the above statement be modified to take care of this behaviour?
> Please reply asap.
> Thanks
> Harish
>
Delete Statement
If I have a table like below. Does anyone know how I write a delete statemen
t
to delete the Timestamps which are the earliest whenever a FromURN is the
same?
NO FromURN ToURN MoveDateMerged Timestamp
-- -- -- --
--
1 100 400 1982-06-15 00:00:00.000 2005-07-28
15:24:29.217
24 100 400 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
15:24:29.217
26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
Want to end up with this.
NO FromURN ToURN MoveDateMerged Timestamp
-- -- -- --
--
24 100 400 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
Thanks for everyone who has helped me since I started this on Monday. I
really do appreciate all the help. I'm finally getting there.Best thing would be to post some ddl and sample data int he group, but it
would be something like:
Delete from sometable
Where st.no =
(
--getting the most recent of them
Select TOP 1 no from sometable st
INNER JOIN
(
--Avaluating all with more than one presence of FromURN
Select FromURN
From Sometable
Group by FromURN
HAVING COUNT(*) > 1
) subquery
ON subquery.FromURN = st.FromURN
Where st.no = sometable.no
order by Timestamp
)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" wrote:
> If I have a table like below. Does anyone know how I write a delete statem
ent
> to delete the Timestamps which are the earliest whenever a FromURN is the
> same?
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 1 100 400 1982-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
>
> Want to end up with this.
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> Thanks for everyone who has helped me since I started this on Monday. I
> really do appreciate all the help. I'm finally getting there.|||Hi Stephen
You can try this as:
DELETE <TableName>
FROM <TableName>
INNER JOIN
(
select fromURN, max(TimeStamp) TimeStamp
from <TableName> group by fromURN
)NewTab
On NewTab.fromURN = <TableName>.fromURN AND NewTab.TimeStamp =
<TableName>.TimeStamp
Just replace <TableName> with the name of your table. Please let me know if
you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Stephen" wrote:
> If I have a table like below. Does anyone know how I write a delete statem
ent
> to delete the Timestamps which are the earliest whenever a FromURN is the
> same?
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 1 100 400 1982-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
>
> Want to end up with this.
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> Thanks for everyone who has helped me since I started this on Monday. I
> really do appreciate all the help. I'm finally getting there.|||On Fri, 29 Jul 2005 02:20:03 -0700, Stephen wrote:
>If I have a table like below. Does anyone know how I write a delete stateme
nt
>to delete the Timestamps which are the earliest whenever a FromURN is the
>same?
>NO FromURN ToURN MoveDateMerged Timestamp
>-- -- -- --
>--
>1 100 400 1982-06-15 00:00:00.000 2005-07-28
>15:24:29.217
>24 100 400 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
>15:24:29.217
>26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>
>Want to end up with this.
>NO FromURN ToURN MoveDateMerged Timestamp
>-- -- -- --
>--
>24 100 400 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>Thanks for everyone who has helped me since I started this on Monday. I
>really do appreciate all the help. I'm finally getting there.
Hi Stephen,
The solutions by Jens and Chandra will delete only the earliest row from
each set of duplicates. If three rows share the same FromURN, the
earliest is deleted and the other two are retained.
If you actually wanted to delete all but the latest row (i.e. in the
example above, delete the two earliest rows), try this:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.FromURN = MyTable.FromURN
AND b.Timestamp > MyTable.Timestamp)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Stephen,
You can try this
Delete from yourTable where NO =
(
select min(NO) FROM YourTable group by
fromURN,convert(varchar,timestamp,112)
)
I Hope this help
With warm regards
Jatinder Singh|||Hi Stephen,
Typo please replace = with in .
Sorry
With warm regards
Jatinder Singh
t
to delete the Timestamps which are the earliest whenever a FromURN is the
same?
NO FromURN ToURN MoveDateMerged Timestamp
-- -- -- --
--
1 100 400 1982-06-15 00:00:00.000 2005-07-28
15:24:29.217
24 100 400 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
15:24:29.217
26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
Want to end up with this.
NO FromURN ToURN MoveDateMerged Timestamp
-- -- -- --
--
24 100 400 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
Thanks for everyone who has helped me since I started this on Monday. I
really do appreciate all the help. I'm finally getting there.Best thing would be to post some ddl and sample data int he group, but it
would be something like:
Delete from sometable
Where st.no =
(
--getting the most recent of them
Select TOP 1 no from sometable st
INNER JOIN
(
--Avaluating all with more than one presence of FromURN
Select FromURN
From Sometable
Group by FromURN
HAVING COUNT(*) > 1
) subquery
ON subquery.FromURN = st.FromURN
Where st.no = sometable.no
order by Timestamp
)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" wrote:
> If I have a table like below. Does anyone know how I write a delete statem
ent
> to delete the Timestamps which are the earliest whenever a FromURN is the
> same?
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 1 100 400 1982-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
>
> Want to end up with this.
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> Thanks for everyone who has helped me since I started this on Monday. I
> really do appreciate all the help. I'm finally getting there.|||Hi Stephen
You can try this as:
DELETE <TableName>
FROM <TableName>
INNER JOIN
(
select fromURN, max(TimeStamp) TimeStamp
from <TableName> group by fromURN
)NewTab
On NewTab.fromURN = <TableName>.fromURN AND NewTab.TimeStamp =
<TableName>.TimeStamp
Just replace <TableName> with the name of your table. Please let me know if
you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Stephen" wrote:
> If I have a table like below. Does anyone know how I write a delete statem
ent
> to delete the Timestamps which are the earliest whenever a FromURN is the
> same?
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 1 100 400 1982-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
>
> Want to end up with this.
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> Thanks for everyone who has helped me since I started this on Monday. I
> really do appreciate all the help. I'm finally getting there.|||On Fri, 29 Jul 2005 02:20:03 -0700, Stephen wrote:
>If I have a table like below. Does anyone know how I write a delete stateme
nt
>to delete the Timestamps which are the earliest whenever a FromURN is the
>same?
>NO FromURN ToURN MoveDateMerged Timestamp
>-- -- -- --
>--
>1 100 400 1982-06-15 00:00:00.000 2005-07-28
>15:24:29.217
>24 100 400 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
>15:24:29.217
>26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>
>Want to end up with this.
>NO FromURN ToURN MoveDateMerged Timestamp
>-- -- -- --
>--
>24 100 400 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>Thanks for everyone who has helped me since I started this on Monday. I
>really do appreciate all the help. I'm finally getting there.
Hi Stephen,
The solutions by Jens and Chandra will delete only the earliest row from
each set of duplicates. If three rows share the same FromURN, the
earliest is deleted and the other two are retained.
If you actually wanted to delete all but the latest row (i.e. in the
example above, delete the two earliest rows), try this:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.FromURN = MyTable.FromURN
AND b.Timestamp > MyTable.Timestamp)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Stephen,
You can try this
Delete from yourTable where NO =
(
select min(NO) FROM YourTable group by
fromURN,convert(varchar,timestamp,112)
)
I Hope this help
With warm regards
Jatinder Singh|||Hi Stephen,
Typo please replace = with in .
Sorry
With warm regards
Jatinder Singh
Delete Statement
Hi,
I would like to delete a record from a table on the condition that a corresponding ID is located in another table, ie. deleting an email message if the user ID is listed as a recipient in a recipient table etc. Here is my SQL statement:
DELETE FROM id_email_message WHERE (id_message IN (SELECT id_message FROM recipients WHERE id_user = 324) AND message.id_message_status = 2) OR (id_message IN (SELECT id_message FROM message WHERE id_owner = 324 and id_message_status = 2))
The problem is the multiple select statements paired with the delete statement is too much overhead for the server and I always get a timeout server error (at least that's what I'm guessing, the error page and tracing isn't much helpful). Is there a more efficient way to do this?
Thanks.
Eitan
DELETE DFROM id_email_message DINNERJOIN recipients RON D.id_message = R.id_messageINNERJOIN message MON D.id_message = M.id_messageWHERE T.id_user = 324AND M.id_message_status = 2AND M.id_owner = 324
DELETE Statement
Hi folks,
I am running the following statement on SQL Server 2000 Database:
DELETE from tablea
WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
AND effective_date < CONVERT(varchar(10),GETDATE(),120)
tablea has effective_date defined as smalldatetime.
When i run the sql, i get the following error:
DELETE failed because the following SET options have incorrect settings:
'ARITHABORT'
The effective_date column is not a computed column. Any idea as to why the
statement should genrate the error.Can you post the table DDL, including indexes?. Is tablea an indexed view or
does tablea have an index on a computed column?
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
effective_date >= CONVERT(char(8), dateadd(day, -3, GETDATE()), 112)
effective_date < CONVERT(char(8), GETDATE(), 112)
AMB
"Ram" wrote:
> Hi folks,
> I am running the following statement on SQL Server 2000 Database:
> DELETE from tablea
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
> tablea has effective_date defined as smalldatetime.
> When i run the sql, i get the following error:
> DELETE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> The effective_date column is not a computed column. Any idea as to why the
> statement should genrate the error.|||Is there a calculated column or an indexed view on the table you are
attempting to delete from?
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:4EF71394-62B6-49C0-8BF6-3FA849F22770@.microsoft.com...
> Hi folks,
> I am running the following statement on SQL Server 2000 Database:
> DELETE from tablea
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
> tablea has effective_date defined as smalldatetime.
> When i run the sql, i get the following error:
> DELETE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> The effective_date column is not a computed column. Any idea as to why the
> statement should genrate the error.|||Hi,
Here is the DDL:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tablea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tablea]
GO
CREATE TABLE [dbo].[tablea] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[iex_id] [int] NOT NULL ,
[mu_id] [int] NOT NULL ,
[effective_date] [smalldatetime] NOT NULL ,
[start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
Indexes:
IDX_mu_id mu_id
IDX_mu_id_agent_id mu_id, iex_id
PK_tblASAgentSchedule iex_id, mu_id, effective_date
Ram
"Alejandro Mesa" wrote:
> Can you post the table DDL, including indexes?. Is tablea an indexed view
or
> does tablea have an index on a computed column?
>
> effective_date >= CONVERT(char(8), dateadd(day, -3, GETDATE()), 112)
> effective_date < CONVERT(char(8), GETDATE(), 112)
>
> AMB
> "Ram" wrote:
>|||I can not reproduce the error.
CREATE TABLE [dbo].[tablea] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[iex_id] [int] NOT NULL ,
[mu_id] [int] NOT NULL ,
[effective_date] [smalldatetime] NOT NULL ,
[start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL,
constraint PK_tblASAgentSchedule primary key (iex_id, mu_id, effective_date)
) ON [PRIMARY]
GO
DELETE from tablea
WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
AND effective_date < CONVERT(varchar(10),GETDATE(),120)
go
drop table tablea
go
AMB
"Ram" wrote:
> Hi,
> Here is the DDL:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tablea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tablea]
> GO
> CREATE TABLE [dbo].[tablea] (
> [row_id] [int] IDENTITY (1, 1) NOT NULL ,
> [iex_id] [int] NOT NULL ,
> [mu_id] [int] NOT NULL ,
> [effective_date] [smalldatetime] NOT NULL ,
> [start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL
> ) ON [PRIMARY]
> GO
> Indexes:
> IDX_mu_id mu_id
> IDX_mu_id_agent_id mu_id, iex_id
> PK_tblASAgentSchedule iex_id, mu_id, effective_date
> Ram
> "Alejandro Mesa" wrote:
>|||The whole design looks wrong. A SQL programmer does not use IDENTITY
for keys, only for display in cursors. Times are not stored in CHAR(n),
but in temporal data types which they do not have to CONVERT() in weird
ways. Audit information is not stored in the tables, but externally
and best done with log tools.
Having something go into effect is a status change, so where is the
status code? You have two identifiers that are not in this table, so
where is the DRI to the base tables that define them? My guess is that
it should look like this:
CREATE TABLE FoobarHistory
(iex_id INTEGER NOT NULL
REFERENCES IEX(iex_id),
mu_id INTEGER NOT NULL
REFERENCES MU(mu_id),
status_code INTEGER NOT NULL, -- let 0 be effective
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means current
PRIMARY KEY (iex_id, mu_id));
Then you can write:
DELETE FROM Foobar
WHERE status = 0;|||Hi,
I dropped and recreated the index on the table.
The delete statement worked fine after that.
Thank you
"Andrew J. Kelly" wrote:
> Is there a calculated column or an indexed view on the table you are
> attempting to delete from?
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4EF71394-62B6-49C0-8BF6-3FA849F22770@.microsoft.com...
>
>
I am running the following statement on SQL Server 2000 Database:
DELETE from tablea
WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
AND effective_date < CONVERT(varchar(10),GETDATE(),120)
tablea has effective_date defined as smalldatetime.
When i run the sql, i get the following error:
DELETE failed because the following SET options have incorrect settings:
'ARITHABORT'
The effective_date column is not a computed column. Any idea as to why the
statement should genrate the error.Can you post the table DDL, including indexes?. Is tablea an indexed view or
does tablea have an index on a computed column?
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
effective_date >= CONVERT(char(8), dateadd(day, -3, GETDATE()), 112)
effective_date < CONVERT(char(8), GETDATE(), 112)
AMB
"Ram" wrote:
> Hi folks,
> I am running the following statement on SQL Server 2000 Database:
> DELETE from tablea
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
> tablea has effective_date defined as smalldatetime.
> When i run the sql, i get the following error:
> DELETE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> The effective_date column is not a computed column. Any idea as to why the
> statement should genrate the error.|||Is there a calculated column or an indexed view on the table you are
attempting to delete from?
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:4EF71394-62B6-49C0-8BF6-3FA849F22770@.microsoft.com...
> Hi folks,
> I am running the following statement on SQL Server 2000 Database:
> DELETE from tablea
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
> tablea has effective_date defined as smalldatetime.
> When i run the sql, i get the following error:
> DELETE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> The effective_date column is not a computed column. Any idea as to why the
> statement should genrate the error.|||Hi,
Here is the DDL:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tablea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tablea]
GO
CREATE TABLE [dbo].[tablea] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[iex_id] [int] NOT NULL ,
[mu_id] [int] NOT NULL ,
[effective_date] [smalldatetime] NOT NULL ,
[start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
Indexes:
IDX_mu_id mu_id
IDX_mu_id_agent_id mu_id, iex_id
PK_tblASAgentSchedule iex_id, mu_id, effective_date
Ram
"Alejandro Mesa" wrote:
> Can you post the table DDL, including indexes?. Is tablea an indexed view
or
> does tablea have an index on a computed column?
>
> effective_date >= CONVERT(char(8), dateadd(day, -3, GETDATE()), 112)
> effective_date < CONVERT(char(8), GETDATE(), 112)
>
> AMB
> "Ram" wrote:
>|||I can not reproduce the error.
CREATE TABLE [dbo].[tablea] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[iex_id] [int] NOT NULL ,
[mu_id] [int] NOT NULL ,
[effective_date] [smalldatetime] NOT NULL ,
[start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL,
constraint PK_tblASAgentSchedule primary key (iex_id, mu_id, effective_date)
) ON [PRIMARY]
GO
DELETE from tablea
WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
AND effective_date < CONVERT(varchar(10),GETDATE(),120)
go
drop table tablea
go
AMB
"Ram" wrote:
> Hi,
> Here is the DDL:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tablea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tablea]
> GO
> CREATE TABLE [dbo].[tablea] (
> [row_id] [int] IDENTITY (1, 1) NOT NULL ,
> [iex_id] [int] NOT NULL ,
> [mu_id] [int] NOT NULL ,
> [effective_date] [smalldatetime] NOT NULL ,
> [start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL
> ) ON [PRIMARY]
> GO
> Indexes:
> IDX_mu_id mu_id
> IDX_mu_id_agent_id mu_id, iex_id
> PK_tblASAgentSchedule iex_id, mu_id, effective_date
> Ram
> "Alejandro Mesa" wrote:
>|||The whole design looks wrong. A SQL programmer does not use IDENTITY
for keys, only for display in cursors. Times are not stored in CHAR(n),
but in temporal data types which they do not have to CONVERT() in weird
ways. Audit information is not stored in the tables, but externally
and best done with log tools.
Having something go into effect is a status change, so where is the
status code? You have two identifiers that are not in this table, so
where is the DRI to the base tables that define them? My guess is that
it should look like this:
CREATE TABLE FoobarHistory
(iex_id INTEGER NOT NULL
REFERENCES IEX(iex_id),
mu_id INTEGER NOT NULL
REFERENCES MU(mu_id),
status_code INTEGER NOT NULL, -- let 0 be effective
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means current
PRIMARY KEY (iex_id, mu_id));
Then you can write:
DELETE FROM Foobar
WHERE status = 0;|||Hi,
I dropped and recreated the index on the table.
The delete statement worked fine after that.
Thank you
"Andrew J. Kelly" wrote:
> Is there a calculated column or an indexed view on the table you are
> attempting to delete from?
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4EF71394-62B6-49C0-8BF6-3FA849F22770@.microsoft.com...
>
>
Sunday, February 19, 2012
Delete Records from Two Tables
Hi,
Is it possible to delete records from two tables with single DELETE statement.
Thanks,
Regards,
Nakkeeran Rengasamy
You can't do it directly.
You have to use the INSTEAD OF TRIGGER (works on Both 2000 & 2005) or OUTPUT clause (if you use SQL Server 2005).
Using Output Clause:
Code Snippet
Create Table #A (
[AId] int ,
[Name] Varchar(100)
);
Insert Into #A Values('1','One');
Insert Into #A Values('2','Two');
Insert Into #A Values('3','Three');
Create Table #B (
[BId] int ,
[AId] int ,
[Desc] Varchar(100)
);
Insert Into #B Values('1','1','SomeText 1.1');
Insert Into #B Values('2','1','SomeText 1.2');
Insert Into #B Values('3','2','SomeText 2.1');
Insert Into #B Values('4','3','SomeText 3.1');
DECLARE @.MyTableVar table (ID int)
Delete #A Output Deleted.AID into @.MyTableVar Where AID=1
Delete From #B Where AId in (Select ID from @.MyTableVar)
Tuesday, February 14, 2012
Delete Multiple IDs
Hi,
I am getting doubles for all my IncidentIDs in IncidentID column.
Ex:
Incident ID
13734
Incident ID
13734
Is there a delete statement to get rid of more then one Replication of the same ID?
Thanks
There are numerous ways available to achieve this
Simplest way is Copy the table Data to temporary table, delete the data in original table, select distinct data from temporary table and insert into the original table
|||isnt there a query i can use?|||try like this
select * into #temptable from yourtable
Go
Delete from yourtable
Go
Insert yourtable
Select distinct * from #temptable
Go
Drop table #temptable
|||where did all my data go in the original table?|||I didn't get you properly, did u executed the mock script ? what's the result?
|||i excecuted the script you have me filling 'yourtable' as the name of my current table and leaving #temptable as is.
I cannot find any data now..
|||Can you provide me your table structure and sample data|||Most of the table looks like this, There are close to 13,000 of these records. For some odd reason now theres 26,000 due to the double copy/replication of each record. i just want to delete the 2nd copy. After I ran your mock query everything is missing.
Columns: Sample Data:
Incident Id 1212
Description Cannot get program to run.
Resolution Double clicked wrong icon.
|||I tested the procedure again, It is working as desired
Let me know the Exact sql statements you used
Subscribe to:
Posts (Atom)