Deleting Spaces!

I am tring to join two tables. There is one problem of course. There is one column I would be able to join the two tables by. This column would be Loc_Code. The only problem is that both columns are not exactly the same. They look like this:

Table 1 Table 2
Loc_Code Loc_Code
A 12345 A12345
A 12346 A12346
A 12347 A12347
A 12348 A12348

I need to erase the spaces that exists in the Loc_Code column in table 1 so that I can join with table 2.

All help would be appreciated.Try this query on your table:

UPDATE [Table 1] SET Loc_Code = Replace([Loc_Code], (Chr(32)), "");

This should get rid of that space.|||You can also use trim -

select a1.col1, a2.col1
from test a, test1 a2
where a1.col3 = trim(a2.col3)|||You can also use trim -

select a1.col1, a2.col1
from test a, test1 a2
where a1.col3 = trim(a2.col3)
!!Cough!!Bullsht!!Cough!!|||aw, c'mon, nocopy, be nice, show the poor guy the right way

since this is the SQL forum, for the SQL language and not any specific implementation thereof, i shall give an SQL solution

estefex, here's your join --
select Table1.foo
, Table2.bar
from Table1
inner
join Table2
on substring(Table1.Loc_Code from 1 for 1)
|| substring(Table1.Loc_Code from 3 for 5)
= Table2.Loc_Code|||Mmm, sorry r937 I'll tone it down. ;)

dj982020 already gave a solution, the replace thing it is.

By the way, your code won't run on my DB. The replace will though.

ss659 No hard feelings mmmkay? ;)|||dj982020's solution will work only in microsoft databases

and if your database does not run standard sql, i suggest you get a better database

:cool: :cool: :cool:|||r937, your code is not robust also.
The replace will plow through as many spaces as you throw at it.
Well I would use it in a join instead of updatin' cause maube the other table wants it this way.

I feel mighty feisty today.|||dj982020's solution will work only in microsoft databases

and if your database does not run standard sql, i suggest you get a better database

:cool: :cool: :cool:
You being bad too.

trim takes one character only 'tsup with dat?
rtrim ltrim kicks bigger A$$.|||maybe estefex's database does not have the replace or trim functions, did you ever consider that?

do you even know what database estefex is running?

no

therefore standard sql is the best solution

and trim will not remove a space from inside a value|||and trim will not remove a space from inside a value

Cough!!True!!Cough!!

Now I KNOW you know standard SQL better than me.
Is this the best standard SQL can do then? :eek:|||well, i don't really want to get into a discussion of whether standard sql is any good or not, or "the best it can do"

all i wanted to do was point out that in this forum, standard sql should be used

especially if the poster does not indicate which database system they're using

i mean, if somebody wanted an oracle solution, there's a forum for oracle

if somebody wanted an access solution, there's a forum for access

if somebody wanted an sql server solution, there's a forum for sql server

if somebody wanted a mysql solution, there's a forum for mysql

what do you think this forum is for?|||R937
Chill, chill. You right. :cool:

Maybe folks ought to mention what DB or DBs they are running.
Then there would be no confusion.
Who needs to read the crystal reports err.. bowl, right? ;)|||!!Cough!!Bullsht!!Cough!!

Funny - it says you have a WHOPPING 23 posts, 6 of which are on this page. Ive noticed you have not actually given any of your OWN ideas, just sat back and critiqued every one elses :) http://www.dbforums.com/showpost.php?p=3671341&postcount=9 One of his better STELLAR posts again..bringing so much to the table. Im sure you have an important job out in the community though so you're too busy to formulate your own thoughts.

And to be honest I never looked at the data for the original post - I just read " I want to erase spaces in one table to join to another" - Trimming a column will take care of the leading and trailing spaces, and yes you're right won't trim within a column. But as we ALL know, most of the data people post is not what actually resides in the actual database. Im sure he really has a table called table1 and table2...riiight..you idiot!|||Funny - it says you have a WHOPPING 23 posts, 6 of which are on this page. Ive noticed you have not actually given any of your OWN ideas, just sat back and critiqued every one elses :) http://www.dbforums.com/showpost.php?p=3671341&postcount=9 One of his better STELLAR posts again..bringing so much to the table. Im sure you have an important job out in the community though so you're too busy to formulate your own thoughts.

And to be honest I never looked at the data for the original post - I just read " I want to erase spaces in one table to join to another" - Trimming a column will take care of the leading and trailing spaces, and yes you're right won't trim within a column. But as we ALL know, most of the data people post is not what actually resides in the actual database. Im sure he really has a table called table1 and table2...riiight..you idiot!
Hah, if you got a high post count you think you are the shit? :D
Maybe you ought to start reading the question before you respond.
Now, I am not going to plow through the millions of your posts and see if they are of the similar quality as the one here. And unlike you I am not going to call you names.

By the way, did you ask r937 if he was offended by my posts? I think not.
Have a nice life.
Apologies for the Idiot will be accepted. :rolleyes:

Deleting SP syntax check please...

I'm trying to write a SP that will delete records from a few tables and then
count to see if any records are for some unknown reason, left over. The
goal is to have the SP return "0" upon successful deletions (used and called
from asp.net code). It it return a value greater than 0 than I know
something went wrong.
Pasted below is my attempt. I keep getting a syntax error near the word
"DELETE" in the first delete command.
What am I doing wrong? Before someone suggests I create relationships
between all these tables, the answer is I can't. I'm working with another
"old-school" developer who doesn't like them and likes to do all his
relationships "programmatically" thru code. My hands are tied so I need to
delete from each table separately.
THANKS!
CREATE PROCEDURE sp_DeletelApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE ID = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count(ID) As Applications FROM Applications WHERE ID = @.intApplicationID) +
(SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GOGroove
> DELETE FROM Applications WHERE ID = @.intApplicationID
Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
> I'm trying to write a SP that will delete records from a few tables and
> then count to see if any records are for some unknown reason, left over.
> The goal is to have the SP return "0" upon successful deletions (used and
> called from asp.net code). It it return a value greater than 0 than I
> know something went wrong.
> Pasted below is my attempt. I keep getting a syntax error near the word
> "DELETE" in the first delete command.
> What am I doing wrong? Before someone suggests I create relationships
> between all these tables, the answer is I can't. I'm working with another
> "old-school" developer who doesn't like them and likes to do all his
> relationships "programmatically" thru code. My hands are tied so I need
> to delete from each table separately.
> THANKS!
>
> CREATE PROCEDURE sp_DeletelApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE ID = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count(ID) As Applications FROM Applications WHERE ID => @.intApplicationID) +
> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>|||Thanks but no luck. I enclosed all my "ID's" in brackets and still the same
error when checking the syntax:
CREATE PROCEDURE spDeleteCapitalApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =@.intApplicationID) +
(SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used and
>> called from asp.net code). It it return a value greater than 0 than I
>> know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>|||:-))),Now I see , you have missed AS in the stored procedure
CREATE PROCEDURE spDeleteCapitalApplication
@.intApplicationID Integer
AS
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
> same error when checking the syntax:
>
> CREATE PROCEDURE spDeleteCapitalApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] => @.intApplicationID) +
> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used
>> and called from asp.net code). It it return a value greater than 0 than
>> I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>|||D'oh!
(slaps forehead)
Thanks!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8PDSaXXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> :-))),Now I see , you have missed AS in the stored procedure
> CREATE PROCEDURE spDeleteCapitalApplication
> @.intApplicationID Integer
> AS
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>
>
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
>> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
>> same error when checking the syntax:
>>
>> CREATE PROCEDURE spDeleteCapitalApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =>> @.intApplicationID) +
>> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left
>> over. The goal is to have the SP return "0" upon successful deletions
>> (used and called from asp.net code). It it return a value greater than
>> 0 than I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the
>> word "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do
>> all his relationships "programmatically" thru code. My hands are tied
>> so I need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>>
>

Deleting SP syntax check please...

I'm trying to write a SP that will delete records from a few tables and then
count to see if any records are for some unknown reason, left over. The
goal is to have the SP return "0" upon successful deletions (used and called
from asp.net code). It it return a value greater than 0 than I know
something went wrong.
Pasted below is my attempt. I keep getting a syntax error near the word
"DELETE" in the first delete command.
What am I doing wrong? Before someone suggests I create relationships
between all these tables, the answer is I can't. I'm working with another
"old-school" developer who doesn't like them and likes to do all his
relationships "programmatically" thru code. My hands are tied so I need to
delete from each table separately.
THANKS!
CREATE PROCEDURE sp_DeletelApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE ID = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count(ID) As Applications FROM Applications WHERE ID =
@.intApplicationID) +
(SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GOGroove
> DELETE FROM Applications WHERE ID = @.intApplicationID
Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
> I'm trying to write a SP that will delete records from a few tables and
> then count to see if any records are for some unknown reason, left over.
> The goal is to have the SP return "0" upon successful deletions (used and
> called from asp.net code). It it return a value greater than 0 than I
> know something went wrong.
> Pasted below is my attempt. I keep getting a syntax error near the word
> "DELETE" in the first delete command.
> What am I doing wrong? Before someone suggests I create relationships
> between all these tables, the answer is I can't. I'm working with another
> "old-school" developer who doesn't like them and likes to do all his
> relationships "programmatically" thru code. My hands are tied so I need
> to delete from each table separately.
> THANKS!
>
> CREATE PROCEDURE sp_DeletelApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE ID = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count(ID) As Applications FROM Applications WHERE ID =
> @.intApplicationID) +
> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>|||Thanks but no luck. I enclosed all my "ID's" in brackets and still the same
error when checking the syntax:
CREATE PROCEDURE spDeleteCapitalApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =
@.intApplicationID) +
(SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationI
D
=@.intApplicationID) +
(SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Groove
> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>|||:-))),Now I see , you have missed AS in the stored procedure
CREATE PROCEDURE spDeleteCapitalApplication
@.intApplicationID Integer
AS
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
> same error when checking the syntax:
>
> CREATE PROCEDURE spDeleteCapitalApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =
> @.intApplicationID) +
> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE Applicatio
nID
> =@.intApplicationID) +
> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>|||D'oh!
(slaps forehead)
Thanks!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8PDSaXXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> :-))),Now I see , you have missed AS in the stored procedure
> CREATE PROCEDURE spDeleteCapitalApplication
> @.intApplicationID Integer
> AS
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>
>
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
>

DELETING ROWS with REFERENTIAL INTEGRITY

hi there!

im having problems deleting rows in a reference table. is there any tools which tables to delete first before deleting the rows in the table which contains the primary key?

i have a lot of tables let say over 300 so its hard for me to guess which comes first... what should i keep in mind deleting rows with a referential integrity?

thank...

1. You can use sys.foreign_keys view to query and follow the data constraints in your table

2. You may try to use cascading referential integrity constraints.

By using cascading referential integrity constraints, you can define the

actions that the SQL Server 2005 takes when a user tries to delete or update a

key to which existing foreign keys point.

The REFERENCES clauses of the CREATE TABLE and

ALTER

TABLE statements support the ON DELETE and ON UPDATE clauses:

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

]

|||

hi carlop!

thank you for reply.... well the table in our database are not set to ON DELETE CASCADE ON due to security reason. so there is no way for me to delete the rows easily, i guess i should track all the tables for their foreign keys and dependent tables :-(

thanks, novelle

|||

hi there...

do u want to delete data from ur selected tables, and dont want their parent tables(PK tables) , to give foreign key errors......if thats the case, u can disable the foreign keys..perform the operation, then enable them again..

else if u want to delete from primary table first...or want to know the related tables, either use database digrams...or maybe this query will help u..

select a.name,c.name as pk_table ,b.name fk_table

from sys.foreign_keys a

inner join sys.sysobjects b on b.id = a.parent_object_id

inner join sys.sysobjects c on c.id = a.referenced_object_id

|||

hi nitin!

thank u for ur quick reply!

the thing is, im copying data from server to server, after copying the data i wanted to deleted this rows i've copied to the source database and offcourse i want to delete correctly.

i dont want to disable the foreignkeys because if my delete script is wrong , i wont able to delete the data correctly.

by the way does this script works on the SQL 2000? because i've tried it and it doesnt work.

thanks novelle.

|||

hi...for 2000 it'll be like

select a.name,c.name as pk_table ,b.name fk_table

from sys.foreign_keys a -- for this pls check the table sysconstraints/sysreferences...i dont quite remember the fields..

inner join sysobjects b on b.id = a.parent_object_id

inner join sysobjects c on c.id = a.referenced_object_id

|||A sql 2k compliant view of foreign keys and primary keys is the following:

CREATE view dbo.foreign_keys as
select cast(f.name as varchar(255)) as fk_name
, r.keycnt
, cast(ft.name as varchar(255)) as foreign_table
, cast(f1.name as varchar(255)) as foreign_col1
, cast(f2.name as varchar(255)) as foreign_col2
, cast(pt.name as varchar(255)) as primary_table
, cast(p1.name as varchar(255)) as primary_col1
, cast(p2.name as varchar(255)) as primary_col2
from sysobjects f
inner join sysobjects ft on f.parent_obj = ft.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects pt on r.rkeyid = pt.id
inner join syscolumns p1 on r.rkeyid = p1.id and r.rkey1 = p1.colid
inner join syscolumns f1 on r.fkeyid = f1.id and r.fkey1 = f1.colid
left join syscolumns p2 on r.rkeyid = p2.id and r.rkey2 = p1.colid
left join syscolumns f2 on r.fkeyid = f2.id and r.fkey2 = f1.colid
where f.type = 'F'
GO

CREATE view dbo.primary_keys as
select distinct
tbl.name TableName,
constrId.name PkName,
col.name ColName,
sik.keyno,
case when ix.indid = 1 then 1 else 0 end IsClustered
from sysobjects tbl
join sysconstraints constr on ( tbl.id = constr.id and tbl.xtype = 'U' and constr.status & 0x0001 = 0x0001 )
join sysobjects constrId on constrId.parent_obj = tbl.id and constrId.xtype = 'PK'
join sysindexes ix on constrId.name = ix.name and ix.id = tbl.id
join sysindexkeys sik on sik.id = tbl.id and sik.indid = ix.indid
join syscolumns col on col.id = tbl.id and sik.colid = col.colid
GO

Deleting rows from many tables

What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:SoderquistString %>"DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID"SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @.State)"><DeleteParameters><asp:ParameterName="UserID"Type="Int32"/></DeleteParameters><SelectParameters><asp:ControlParameterControlID="DropDownList1"Name="state"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource>

The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:

DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID

DELETE FROM [photo] WHERE [UserID] = @.UserID;

DELETE FROM [album] WHERE [UserID] = @.UserID;

DELETE FROM [comment] WHERE [UserID] = @.UserID;

...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!

Try this. I tested for two tables

DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID;DELETE FROM [photo] WHERE [UserID] = @.UserID;DELETE FROM [album] WHERE [UserID] = @.UserID;DELETE FROM [comment] WHERE [UserID] = @.UserID"

|||

Thanks for the info. I've tried that already and I still get an error:

Invalid column name 'UserID'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid column name 'UserID'.

Any suggestions?

|||

Do you have UserID column for all tables you are going to delete?

Since I tested the syntax for only two tables, I will test for more tables to confirm whether the syntax works for multiple tables.

|||

This DeleteCommand works on the dummy test tables in SQL Server 2005:

DeleteCommand="DELETE FROM [T1] WHERE [ID1] = @.ID;DELETE FROM [T2] WHERE [ID1] = @.ID;DELETE FROM [T3] WHERE [ID1] = @.ID;DELETE FROM [T4] WHERE [ID1] = @.ID;DELETE FROM [T5] WHERE [ID1] = @.ID"<DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters>

But if you click delete rows too fast, you may get error message for the parameter. You should be able to delete from a stored procedure with error checking from there.

|||

Limno, thanks and I appreciate the help you have given me. I was looking over my tables and noticed that there is a difference in one of them. I'm going to write out the four tables and a few columns that I have within them and hopefully this will help clear a few things up that I'm having trouble with:

alumni - UserID (PK), FirstName, LastName, UserName

photo - PhotoID (FK), AlbumID, PhotoPath

album - AlbumID (FK), UserID, PhotoPath, AlbumName

comments - CommentID (FK), UserID, Comment, UserName, CommentDate

What I have is one primary_key, which is UserID and the other three are foreign_keys (PhotoID, AlbumID, and CommentID). All of the foreign keys have UserID in them except for the photo table because it associates with the album table that has UserID in it. That was the one difference that I forgot about.

So is it possible, with the way I have this set up for me to be able to delete a user from the alumni table along with the other information in the other tables that are associated with that user? I can delete a user if there are no albums, photos or comments connected with that person, but it's when they have something connected to them a problem comes up because of certain constraints.

Thank you in advanced!

|||

Hello:

I tested with the following DELETECommand:

DeleteCommand="DELETE FROM [photo] WHERE ([phtoID] IN (SELECT photoID FROM album WHERE userID = @.userID)); DELETE FROM [comments] WHERE [userID] = @.userID;DELETE FROM [alumni] WHERE [userID] = @.userID;DELETE FROM [album] WHERE [userID] = @.userID"

It seems that the cascading delete through a trigger would be a perfect fit for your case. However, I rearrange the delete action sequence for your case in this DeleteCommand. I tested with success. But you need to test on your data to make sure it works properly. If you want to control the flow, you may need to go with the trigger and/or stored procedure to handle your multiple cascading deletion.

|||

It worked! Thank you for your time, I really appreciate it. It looks like my problem was with the way I was deleting the information from the photo table and how it was related to the albums. This peace of code here...

DELETE FROM [photo] WHERE ([phtoID] IN (SELECT photoID FROM album WHERE userID = @.userID));

...did the trick.

Just for my understanding, what did you mean by "If you want to control the flow, you may need to go with the trigger and/or stored procedure to handle your multiple cascading deletion"? Thanks again!

|||

The code works but you have no way to know if something wrong with it during deletion. I did run into a few times during my test. That is what I mean. If the project becomes critical, you can go with the trigger and stored procedure. Or You can use your own error catch logic from code behind. Just my thought for your situation. I want to know what will happen if there is an error for learning purpose.

I am glad this hack works for you now. If you are interested, you can search for the topic on cascading deleting. The constrains on the foreign keys are very usful to maintain the integrity of your data set.

Deleting records from two tables

im having a very blond day .. carnt get my head round this today
Im rining SQl2005
i have two tables ( A & B ) A contains the 'master' record abd B contains
the 'detail' For every record in A there will be a minimum of 1 record in B
to a max of 1000000
Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
Table A also has a field 'Status'
What i want to do is create a stored procedure that deletes all records in
the Master (A) and Detail(B) tables when A.Status = 'T'
like i said this morning my minds a blanksomething like this?
begin tran
delete from B
from details B
where exists (select 1 from master_tbl A
where a.LedgerRef = b.LedgerRef
and a.status = 't')
delete from master_tbl
where status = 't'
commit tran
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try this:
USE tempdb
GO
CREATE TABLE A
(
id int,
status char,
LedgerRef int
)
GO
CREATE TABLE B
(
LedgerRef int,
somedata varchar(100)
)
GO
INSERT A VALUES(1, 'T', 10)
INSERT A VALUES(2, 'F', 20)
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(20, 'don''t delete it')
DELETE B
FROM B JOIN A ON B.LedgerRef = A.LedgerRef
WHERE A.Status = 'T'
DELETE A
WHERE Status = 'T'
Greetings,
Urs
"Peter Newman" wrote:

> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B contain
s
> the 'detail' For every record in A there will be a minimum of 1 record in
B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>|||Use CASCADE DELETE on TableB and you will only have to manage TableA.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:08FA6CBE-88EC-4775-80A1-6413FE99A16A@.microsoft.com...
> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B
> contains
> the 'detail' For every record in A there will be a minimum of 1 record in
> B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>

Deleting records from multiple tables in SQL server

I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.

I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):

Table 1: Classes --Columns: ClassID, ClassName

Table 2: Roster--Columns: ClassID, StudentID, Student Name

Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName

Table 4: Scores--StudentID, AssignmentID, Score

What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.

What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.

The stored procedure I created was

DELETE FROM Classes WHERE ClassID=@.classid

I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:

The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.

What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?

I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.

Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.

Further, is there something else I need to do besides assigning primary keys and foreign keys?

WHen you create a foreign key, there are some additional options you have to set to tell it to do the cascade delete.

If you are creating the foreign key through T-SQL you must append the ON DELETE CASCADE option to the foreign key:

Code Snippet

ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> FOREIGN KEY (<columnname(s)>)
REFERENCES <referencedtablename> (<columnname(s)>)

ON DELETE CASCADE;

If using SSMS, modify the table and go to where you created the relationship. If you look around in there you should see an option to set the "Delete Rule" Set that to CASCADE.

|||

OK, the concept of deleting rows from multiple tables in a single delete statement cannot be done in just that statement. There is the concept of triggers on the tables that do deletes in a cascading style, but I would not recommend you do it that way for sake of control of the actions of the data.

But I udnerstand what you want to do, and the best way to explain it is this:

Say you have these tables and each one has a relationship up the chain. If you have Classes, and they are on Rosters, and Assignments are given to Classes and Scores have Students and Assignments you have to see which is the last one in the chain.

So in your case you have Classes that is the base and then you have Classes in Rosters (this is the next level) and you have Classes in Assignments (same level as Rosters).

So you have a relationship like

Classes - ClassID

|__Roster - ClassId

|__Assignments - ClassId

|Scores - AssignmentId

So say you no longer had ClassId 3 and you wanted to just get rid of all the records that are associated with ClassId 3. Here are the steps that you would need to take. You would delete in the reverse order than you inserted.

So in this case, you would want to use the ClassId = 3 to get all the assignments that have that ClassId and delete the Scores that have the AssignmentId and then delete the Assignments with the ClassId = 3

Then you would delete the Rosters with the ClassId = 3 and then finally delete the Classes with ClassId = 3

SQL:

DELETE Scores

FROM Assignments A

INNER JOIN Scores S ON A.AssignmentId = S.AssignmentId

WHERE A.ClassId = 3

DELETE Assignments

WHERE ClassId = 3

DELETE Roster

WHERE ClassId = 3

DELETE Classes

WHERE ClassId = 3

So you really just need to delete the Foreign Key tables records with the Primary Key record in it first and then delete the Primary Key records in the Primary table or Base table last.

HTH.

Ben Miller

|||I disagree with you an the answer that this cannot be done within one statement as the suggestions from Andrew about Cascading deletes should solve the problem, if the architecture is appropiate for the original poster.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Ben

I also disagree with your presentation. With a properly established set of relationships, CASCADE DELETE works wonderfully. The PK-FK relationships must be properly set-up, and there cannot be any circular relationships.

So you have a relationship like

Classes - ClassID

|__Roster - ClassId

|__Assignments - ClassId

|Scores - AssignmentId

In this situation, a deletion on [Classes] will remove related data from all lower tables. Deleting [Assignments] will also delete related data from [Scores]. A deletion on [Roster] or [Scores] will only affect those tables.

deleting records

I have a couple SQL tables that have been appended to daily over the last two years. There is now about 50,000,000 records in the table. Does anyone know the fastest way to delete records before a certain date to shorten these tables? Delete queries and everything else I've tried is taking way too long.

i'm guessing there is no index on the date column - so you are suffering a table scan - you'll just have to suck it up and let the delete take however long it takes.

If this will be an ongoing requirement (i.e. purge this table) - why don't you add an index?

.....gtr

|||

Thanks for your response

This table was set up way before my time, but there is no index. I am still relatively new with MS Sql so am not very famililiar w/ indexing. Is this all I have to do?

CREATE INDEX IDX_Daily_snapshot_INV_File_Date
on Daily_snapshot_INV (File_Date)

After this, do I just query the table normally and it will bypass the table scan?

|||

yup, that's all there is to it

.....gtr

|||

>> it will bypass the table scan?<<

This is not possible to guess, but if you are only deleting a few rows, like say 10% or less (VERY ROUGH estimate) this should do the trick.

Even so, if you have 50 million rows over 2 years it is going to take a while to delete a large number of these rows regardless. If it is slow, try deleting only a small number of rows, like a days, weeks, or months worth at a time (instead of a year at a time) which will depend a lot on your hardware.

|||

Thank you for your answers. I know now that indexing will help me search but unfortunately there is nothing to help the process of of the actual deletion. I am going to run a delete query in Query Analyzer over the weekend when our server is not being used.

Matt

|||

Here is a small example that you can use to experiment with

And you can modify it to delete your data in batches

this code creates a table that has the au_id from the authors table

The delete query has a where clause that will delete exactly 9 rows

the code will run until @.@.rowcount =0 and it will delete in batches of 2

This of course is just to illustrate this concept but you probably want to have a much higher number perhaps 10000

use pubs
set nocount on
create table SomeTable (ID varchar(49))
insert into SomeTable
select au_id from authors

declare @.count int
select @.count = count(*) from SomeTable

print 'Count before delete == ' + convert(varchar(10),@.count)

declare @.rowcount int
set rowcount 2 --delete in batches of 2
select @.rowcount =666 --initialize to <> 0

while @.rowcount <> 0 -- do until we are done
begin
-- the table should be 9 rows less after we are done
delete SomeTable
where ID like '7%'
or ID like '8%'

select @.rowcount =@.@.ROWCOUNT

end

set rowcount 0 --very important to set this back to 0

select @.count = count(*) from SomeTable

print 'Count after delete == ' + convert(varchar(10),@.count)
set nocount off

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Is there an advantage of doing it that way when I could do this:

DELETE FROM dbo.Daily_snapshot_INV
WHERE (File_date < CONVERT(DATETIME, '2005-04-01 00:00:00', 102))

The problem is that this probably consists of 20,000,000 rows so instead of one query to delete them all I will need to delete a month or half month at a time. Each day has about 120,000 rows. This table was created in Oct, 2004.

Like I said above, I am relatively new at SQL so any help in understanding is appreciated.

|||

The advantage is that you don't have to write a lot of WHERE statements

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Since this is the first time you are deleting from the table - yes, you are just going to have to let the thing run - deleting many rows (and logging) just plain takes time.

In the future, as this becomes a regular process (perhaps monthly) the index will help a great deal.

.....gtr

Deleting Pub Tables

I previously had a publication set up on my database. I've dropped it but
still have a number of system tables in my database of the form
conflict_dbnamePub_tablename. Is there a way I can delete these
--
Thanks
RonFTake a look at this article:
http://www.mssqlserver.com/replicat...on_cleanup.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"RonF" <RonF@.discussions.microsoft.com> wrote in message
news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> I previously had a publication set up on my database. I've dropped it but
> still have a number of system tables in my database of the form
> conflict_dbnamePub_tablename. Is there a way I can delete these
> --
> Thanks
> RonF|||Thanks for the assistance!
Ron
"Dejan Sarka" wrote:

> Take a look at this article:
> http://www.mssqlserver.com/replicat...on_cleanup.asp.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "RonF" <RonF@.discussions.microsoft.com> wrote in message
> news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
>
>

Deleting Pub Tables

I previously had a publication set up on my database. I've dropped it but
still have a number of system tables in my database of the form
conflict_dbnamePub_tablename. Is there a way I can delete these
--
Thanks
RonFTake a look at this article:
http://www.mssqlserver.com/replication/bp_manual_replication_cleanup.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"RonF" <RonF@.discussions.microsoft.com> wrote in message
news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> I previously had a publication set up on my database. I've dropped it but
> still have a number of system tables in my database of the form
> conflict_dbnamePub_tablename. Is there a way I can delete these
> --
> Thanks
> RonF|||Thanks for the assistance!
Ron
"Dejan Sarka" wrote:
> Take a look at this article:
> http://www.mssqlserver.com/replication/bp_manual_replication_cleanup.asp.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "RonF" <RonF@.discussions.microsoft.com> wrote in message
> news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> > I previously had a publication set up on my database. I've dropped it but
> > still have a number of system tables in my database of the form
> > conflict_dbnamePub_tablename. Is there a way I can delete these
> > --
> > Thanks
> > RonF
>
>

Deleting Pub Tables

I previously had a publication set up on my database. I've dropped it but
still have a number of system tables in my database of the form
conflict_dbnamePub_tablename. Is there a way I can delete these
Thanks
RonF
Take a look at this article:
http://www.mssqlserver.com/replicati...n_cleanup.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"RonF" <RonF@.discussions.microsoft.com> wrote in message
news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> I previously had a publication set up on my database. I've dropped it but
> still have a number of system tables in my database of the form
> conflict_dbnamePub_tablename. Is there a way I can delete these
> --
> Thanks
> RonF
|||Thanks for the assistance!
Ron
"Dejan Sarka" wrote:

> Take a look at this article:
> http://www.mssqlserver.com/replicati...n_cleanup.asp.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "RonF" <RonF@.discussions.microsoft.com> wrote in message
> news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
>
>

Deleting primary key when no foreign records exist?

Is there a trigger that would handle this situation? I have a 1 to
many between two tables and would like the primary key deleted if the
last foreign record is deleted.
Thanks in advance!Sure, in an AFTER trigger on the child table, something like (including a
scenario to try it out.):
set nocount on
go
create table parent
(
parentKey int primary key
)
create table child
(
childKey int primary key,
parentKey int foreign key references parent(parentKey)
)
insert into parent
select 1
union all
select 2
union all
select 3
insert into child
select 1,1
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 5,2
union all
select 6,2
union all
select 7,3
union all
select 8,3
go
create trigger child$deleteTrigger
on child
after delete
as
--be sure to add error handling
delete parent
--this gets all parent rows that are related to the deleted child rows based
on the migrated key from the parent
where exists (select 1
from deleted
where parent.parentKey = deleted.parentKey)
--this excludes parents where a child still exists
and not exists (select 1
from child
join deleted
on child.parentKey =
deleted.parentKey
where parent.parentKey = deleted.parentKey)
go
delete child where parentkey = 1
select *
from parent
left outer join child
on parent.parentKey = child.parentKey
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1139625347.478136.146410@.g14g2000cwa.googlegroups.com...
> Is there a trigger that would handle this situation? I have a 1 to
> many between two tables and would like the primary key deleted if the
> last foreign record is deleted.
> Thanks in advance!
>|||Many thanks Louis!|||Yes, you can do this with a trigger, if you like to write procedural,
proprietary code. Look for a COUNT(*) = 0 in a PK-FK join.
Another way to do this is to put one "child" item the same table as the
"parent" since you seem to require at least one "child" as part of the
design. When you delete the "only child", you have to delete the
parent.
Oh, did I mention that the code is messy?|||> Another way to do this is to put one "child" item the same table as the "p
arent"
Joe,
Are you saying your solution conforms to 3NF?
What if later you'll need to delete the child row stored along with the
parent? You'll have to move another row from the child table to the
parent one. Are you claiming it's less messy than Louis's solution?
Also instead of selects against the child table you'll have to select
against a union of the child and the parent, right?
How would you enforse a unique constraint on the child?sql

deleting multiple tables through the Management Studio GUI

is there a way to delete multiple tables using the Management Studio GUI?In the Object Explorer Detail pane, while holding down the [ctrl] key, select the tables to delete, and the press the [Delete] key.

Deleting matching records

I need your help in MS Access…
There are 2 tables Table A with 50 records and Table B with 5 records
(similar records), I want to delete the 5 records of table B from Table A so
that in the end Table A should have 45 records (assuming all 5 records of
Table B are in Table A)…
Please help me…Mir Khan wrote:
> I need your help in MS Access...
... but this is a SQL Server group!

> There are 2 tables Table A with 50 records and Table B with 5 records
> (similar records), I want to delete the 5 records of table B from Table A
so
> that in the end Table A should have 45 records (assuming all 5 records of
> Table B are in Table A)...
> Please help me...
In SQL Server:
DELETE FROM A
WHERE EXISTS
(SELECT *
FROM B
WHERE B.col1 = A.col
AND B.col2 = A.col2
AND ... etc ) ;
David Portas
SQL Server MVP
--sql

Deleting leading 0's in numbers stored in a text field.

I am trying to use several tables that have one 10-character text field in
common. Most of the records have a numeric expression, but some tables have leading
0's, and some don't.
I can't cast the field to numbers because there are some records that have
letters also.
What function can I use to get rid of all the 0s at the left of each record?
(Sort of a LTRIM function that gets rid of 0s instead of spaces).

Thanks!

While I am not aware of any built-in function to perform this (maybe a good SQLCLR function candidate :) ), this TSQL will work as tested below...

declare @.Field varchar(10)

declare @.i int

set @.Field = '000123405'

set @.i = 1

--remove leading 0s?

if charindex('0', @.Field) = 1

begin

while @.i <= Len(@.Field)

begin

--character a 0?

if charindex('0',@.Field,@.i) = @.i

begin

set @.Field = substring(@.Field, (@.i + 1), (len(@.Field)-@.i))

end

else

begin

break

end

--increment counter

set @.i = @.i + 1

end

end

select @.Field

|||

Leading zeroes are fine for casting character values to numeric/integer/money data types. So it should be fine without doing any trimming. For the rows that have letters you can filter those using a case expression like:

case when col not like '%[^0-9]%' then cast(col as int) end

or below although this checks for conversions to integer/numeric/money data types

case when isnumeric(col) = 1 then cast(col as int) end

And if you want to strp the leading zeroes you can use the expression below:

substring(col, patindex('%[123456789]%', col), 8000 /* 4000 if col is Unicode */)

|||the code above has a bug.. here is the correct and much efficient code

DECLARE @.i INT
,@.output VARCHAR(MAX)
,@.Input varchar(max)

set @.Input = '0012321'

SET @.i = 1

IF CHARINDEX('0', @.Input) = 1
BEGIN
WHILE @.i <= LEN(@.Input)
BEGIN

IF CHARINDEX('0',@.Input,@.i) = 0
BEGIN
SET @.output = SUBSTRING(@.Input,@.i,LEN(@.Input))
BREAK
END

SET @.i = @.i + 1

END

END

RETURN @.output

Deleting Indexes

Is there a way to delete the indexes on all the tables for a specific
database in SQL Server using Query Analyzer without knowing the exact name o
f
the Index? I am able to do this using SQLDMO, but I need to know if there is
a way to do this using Query Analyzer.
ThanksYou might want to look at the sysindexes table
Select * from sysindexes
I would create a cursor to loop through the sysindexes table and issue the
command
Drop Index TableName.IndexName
Both TableName and IndexName can be found in Sysindexes table
HTH
Ed
"JD" wrote:

> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
of
> the Index? I am able to do this using SQLDMO, but I need to know if there
is
> a way to do this using Query Analyzer.
> Thanks|||This will not take care of indexes implicitly created by, say PK and Unique
constraints, but it may be a start:
select 'DROP INDEX '+object_name(id)+'.'+object_name(object_id(name))
from sysindexes
WHERE objectproperty(object_id(name), 'IsMsShipped') = 0
AND objectproperty(id, 'IsMsShipped') = 0
AND keycnt = 0
"JD" <JD@.discussions.microsoft.com> wrote in message
news:C554DC0B-22B6-4763-8A10-052843C5D47C@.microsoft.com...
> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
> of
> the Index? I am able to do this using SQLDMO, but I need to know if there
> is
> a way to do this using Query Analyzer.
> Thanks|||hi,
if you know the name of the table using sp_help <table> you obtain the name
of its indexes.
regards,
"JD" wrote:

> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
of
> the Index? I am able to do this using SQLDMO, but I need to know if there
is
> a way to do this using Query Analyzer.
> Thanks

Deleting from three tables

I'm triyng to delete from three tables.

Issue Table:

Column Name Data Type Allow Nulls issueID int Unchecked name varchar(50) Unchecked title varchar(100) Checked description varchar(500) Checked crntIssue bit Checked frntPage int Checked archived bit Checked navOrder int Checked dateCreate datetime Unchecked

Outlook Table:

Column Name Data Type Allow Nulls ID int Unchecked menu bit Checked mnuOrder int Checked mnuLevel int Checked parent int Checked issueID int Unchecked masterPage varchar(100) Unchecked visible bit Checked name varchar(50) Checked title varchar(250) Unchecked description varchar(1000) Checked summary text Checked contents text Checked image varchar(50) Checked imgCaption varchar(1000) Checked approve bit Checked createDate datetime Unchecked

Links Table:

Column Name Data Type Allow Nulls lnkID int Unchecked linkFromID int Unchecked linkToID int Unchecked

Issue table contais all the magazine issues. Outlook table contains all the pages of the Issue table. And the Links table contains the links or connection between parent page and child page. So here's what I wanted to do. When I click the delete issue button, I want to delete any pages, links, and issue from three tables that matches the issue ID that I wanted to delete. So for example, if I wanted to delete issueID 2, all the pages in the Outlook table and any links of those pages(lnkFromID) that are in the Links table should be deleted too. The lnkFromID and lnkToID are foriegn key of Outlook.ID table. The lnkFromID is the parent and lnkToID is the child.

I was wondering that maybe I can three delete statements for three tables. Is this a possibility? So any help is much appreciated.

Again, help is still needed. It seems to me that these statements will delete from three tables.

DELETE * FROM [OLlinks],[Outlook] WHERE ([OLlinks].[linkToID] = [Outlook].[ID] AND [Outlook].[issueID] = @.ID)

DELETE * FROM [OLissue],[Outlook] WHERE ([OLissue].[issueID] = [Outlook].[issueID] AND [OLissue].[issueID] = @.ID)

However, is this a best practice and how do I execute two delete statemens in one call?

deleting from one table based on rows in another

I have 2 tables: OrderHeaders and OrderLines which has a one-to-many relationship.
Depending on if the field CreateDate in OrderHeaders has expired som date, I want to delete the corresponding rows in OrderLines AND OrderHeaders... but how?

The tables share the fields CompanyID, CustomerID, OrderNO.If OrderLines table was created with a foreign key constraint on OrderHeader with 'ON DELETE CASCADE' option, alll you have to do is:

Delete From OrderHeader
Where CreateDate <= ExpireDate;

Else, you need first to remove the OrderLines:

Delete From OrderLines L
Where Exists (
Select 1 From OrderHeader H
Where H.CompanyID = L.CompanyID
And H.CustomerID = L.CustomerID
And H.OrderNO = L.OrderNO
And L.CreateDate <= ExpireDate);

And then execute the delete of OrderHeader.|||You can have a daily process (procedure/function) scheduled to run at night to check for any such orders and then delete from OrderLines first and then from OrderHeaders.

Or you could use DBMS_JOB to schedule the procedure/function to do this. Once you have written the correct procedure/function to do this, it is easy to schedule using DBMS_JOB.

Say if you have a procedure 'test_job', you can schedule it as below :

declare
l_job number;
begin
dbms_job.submit(l_job, 'test_job;',sysdate+1/200);
end;
/
commit
/

Hope this helps !!

Originally posted by caf78
I have 2 tables: OrderHeaders and OrderLines which has a one-to-many relationship.
Depending on if the field CreateDate in OrderHeaders has expired som date, I want to delete the corresponding rows in OrderLines AND OrderHeaders... but how?

The tables share the fields CompanyID, CustomerID, OrderNO.

Deleting from a table

Im working on a project that import data into a gash db. there are about 15
tables in this db and each phase of testing requires the tables to be
cleared. This seems to take ages when i got run a query as follows
delete table1
delete table2
etc
any suggestionon speeding this up ?use
truncate table table1
Use this.. if you don't need the data again. Its faster because its not
logged.
Hope this helps.
--
"Peter Newman" wrote:

> Im working on a project that import data into a gash db. there are about 1
5
> tables in this db and each phase of testing requires the tables to be
> cleared. This seems to take ages when i got run a query as follows
> delete table1
> delete table2
> etc
> any suggestionon speeding this up ?|||You might consider using TRUNCATE TABLE instead. This statement generally
uses few locks and less log space.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:59687486-40A1-43C0-BBBA-1068519772F2@.microsoft.com...
> Im working on a project that import data into a gash db. there are about
> 15
> tables in this db and each phase of testing requires the tables to be
> cleared. This seems to take ages when i got run a query as follows
> delete table1
> delete table2
> etc
> any suggestionon speeding this up ?

Deleting from 2 tables?

Hi, I am pretty new to SQL and know that I am probably going around this the wrong way.

I want to make a stored proc that deletes rows from table 1 and delete rows from table 2 where the common link is the id.

Any help would be greatly appreciated!

Many thanks

moopIf you have a relationship set between the tables, you can set the action in table 1 to "On Delete Cascade".
|||*blush*

Thanks for the reply. I just realised that unlike mySql you can issue two deletes in one stored proc like so

DELETE FROM tblSupplierType WHERE supplierType_id = @.supplierType_id;
DELETE FROM tblSuppliers WHERE supplierType_id = @.supplierType_id

MAny thanks for the reply|||I would probably still use the On Delete Cascade option, because ifsomeone comes along and changes your SQL statement later, you'll have alot of stranded Supplier records. With On Delete Cascade, it allhappens as part of the original delete.
Either way I would suggest wrapping those two statements in atransaction, and you'll need to reverse the order in which you've shownthem.
|||

I'm trying this using the Club Starter Kit, creating a relationship between the Albums and Images table, where the PK Album ID in the Albums table has a relationship to the Images table via the album field.

When I create the relationship and use the OnDeleteCascase option, I'm receiving this error:

'Albums' table saved successfully
'images' table
- Unable to create relationship 'FK_images_Albums'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_images_Albums". The conflict occurred in database "C:\DOCUMENTS AND SETTINGS\GARY\MY DOCUMENTS\VISUAL STUDIO 2005\WEBSITES\MyWebSite\MyWebSite.MDF", table "dbo.Albums", column 'albumid'.

any thoughts on what might be wrong?

I know the PK ablumid is an identity field. Does that matter?

Thanks,

Gary

|||

Disregard my previous post. I was able to get the Cascade to work with Delete.

Thanks,

Gary