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

Deleting all SPROCS and UDFs

I want to be able to delete all the SPROCS and UDF's in my DATABASE - pretty
much bringing it down to just TABLE - data only.
I want to do this in a QUERY - in query analyzer.
I do not want to hardwire the names of each SPROC and UDF as more and more
get added every day.
Any ideas'Take a look at the sysobjects table.
There are several ways to do this.. My quick and dirty is to run a query
similar to this... I don't have a SQL Server handy, so you will have to
edit this appropriately...
SELECT 'DROP PROCEDURE ' + Name FROM sysobjects WHERE TYPE = 'P' ORDER BY
Name
Run this and you should output something like:
DROP PROCEDURE usp_Foo1
DROP PROCEDURE usp_Foo2
DROP PROCEDURE usp_Foo3
Then copy and paste that into the top half of the window and execute it.
You may run into trouble if you have dependencies between these objects...
There are better and more complicated ways to do this, of course this is
just a quick and dirty.
Rick
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:1B6F21DC-BB0E-4910-955E-2BF0CF54F8F5@.microsoft.com...
> I want to be able to delete all the SPROCS and UDF's in my DATABASE -
pretty
> much bringing it down to just TABLE - data only.
> I want to do this in a QUERY - in query analyzer.
> I do not want to hardwire the names of each SPROC and UDF as more and more
> get added every day.
> Any ideas'
>|||Thanks for the direction - I used the following:
DECLARE @.PROCNAME VARCHAR(255)
DECLARE @.PARENT VARCHAR(255)
DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='P'
and LEFT(NAME,3)<>'dt_' ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME
EXEC ('DROP PROCEDURE '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='FN'
ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME
EXEC ('DROP FUNCTION '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='V'
AND LEFT(NAME,3)<> 'sys' ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME
EXEC ('DROP VIEW '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
DECLARE DROP_CURSOR CURSOR FOR select s1.name,s2.name from sysobjects s1
left join sysobjects s2 on s2.id=s1.parent_OBJ where s1.type='F' ORDER
BY s1.NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME,@.PARENT
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME+' '+@.PARENT
EXEC ('ALTER TABLE '+@.PARENT+' DROP CONSTRAINT '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME,@.PARENT
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
DECLARE DROP_CURSOR CURSOR FOR select s1.name,s2.name from sysobjects s1
left join sysobjects s2 on s2.id=s1.parent_OBJ where s1.type='K' ORDER
BY s1.NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME,@.PARENT
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME+' '+@.PARENT
EXEC ('ALTER TABLE '+@.PARENT+' DROP CONSTRAINT '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME,@.PARENT
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
"Rick Sawtell" wrote:
> Take a look at the sysobjects table.
> There are several ways to do this.. My quick and dirty is to run a query
> similar to this... I don't have a SQL Server handy, so you will have to
> edit this appropriately...
> SELECT 'DROP PROCEDURE ' + Name FROM sysobjects WHERE TYPE = 'P' ORDER BY
> Name
> Run this and you should output something like:
> DROP PROCEDURE usp_Foo1
> DROP PROCEDURE usp_Foo2
> DROP PROCEDURE usp_Foo3
> Then copy and paste that into the top half of the window and execute it.
>
> You may run into trouble if you have dependencies between these objects...
> There are better and more complicated ways to do this, of course this is
> just a quick and dirty.
>
> Rick
>
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:1B6F21DC-BB0E-4910-955E-2BF0CF54F8F5@.microsoft.com...
> > I want to be able to delete all the SPROCS and UDF's in my DATABASE -
> pretty
> > much bringing it down to just TABLE - data only.
> >
> > I want to do this in a QUERY - in query analyzer.
> >
> > I do not want to hardwire the names of each SPROC and UDF as more and more
> > get added every day.
> >
> > Any ideas'
> >
> >
>
>

DELETE Trigger

Hi,
I am pretty sure this is fairly straightforward, I would like to use a
trigger to delete an existing record from a table where the value of certain
fields match those of the record to be inserted.
Thanks.Hi Redowl,
CREATE TRIGGER SomeDelTrigger ON SomeTable
FOR INSERT
AS
BEGIN
DELETE
FROM SomeOtherTable
INNER JOIN INSERTED
ON SomeOtherTable.COl01 = INSERTED.Col01
-- (...) other columns to join on
END
HTH, Jens Suessmeyer|||Without knowing the details of your underlying tables
CREATE TRIGGER deleteRows ON [YourTable]
AFTER INSERT
AS
DELETE FROM [YourTable] WHERE [YourTable].col1=inserted.col1
AND [YourTable].col2=inserted.col2 --check to match more if necessary
Keep in mind that an AFTER trigger will only execute if the statement that
caused the trigger to fire would have normally succeeded. If you are trying
to delete a row that would have caused a violation of a constraint, use a
INSTEAD OF trigger.
--
"Redowl" wrote:

> Hi,
> I am pretty sure this is fairly straightforward, I would like to use a
> trigger to delete an existing record from a table where the value of certa
in
> fields match those of the record to be inserted.
> Thanks.
>
>|||CREATE TRIGGER SomeDelTrigger ON SomeTable
FOR INSERT
AS
BEGIN
DELETE
FROM SomeotherTable
INNER JOIN INSERTED
ON SomeotherTable.Col1 = INSERTED.Col1
--(...) and so on, name the joined columns
END
HTH, jens Suessmeyer.|||Thanks everybody for the responses
Mark, will your suggestion not delete the newly added row as well ?
"Mark Williams" wrote:
> Without knowing the details of your underlying tables
> CREATE TRIGGER deleteRows ON [YourTable]
> AFTER INSERT
> AS
> DELETE FROM [YourTable] WHERE [YourTable].col1=inserted.col1
> AND [YourTable].col2=inserted.col2 --check to match more if necessary
>
> Keep in mind that an AFTER trigger will only execute if the statement that
> caused the trigger to fire would have normally succeeded. If you are tryin
g
> to delete a row that would have caused a violation of a constraint, use a
> INSTEAD OF trigger.
> --
>
> "Redowl" wrote:
>|||Jens,
Thanks for the response. I want to delete an existing row from the same
table I am inserting into.
I want to delete any existing rows that match a certain criteria. e.g A new
row could have surname = 'Jones' and firstname = 'Bob' and age='30'. I want
to check if there is already a row with surname = 'Jones' and firstname =
'Bob' and delete this?
Grateful for any help.
"Jens" wrote:

> CREATE TRIGGER SomeDelTrigger ON SomeTable
> FOR INSERT
> AS
> BEGIN
> DELETE
> FROM SomeotherTable
> INNER JOIN INSERTED
> ON SomeotherTable.Col1 = INSERTED.Col1
> --(...) and so on, name the joined columns
> END
>
> HTH, jens Suessmeyer.
>

Delete rows in relational database

Hi,
I have a relational database. Pretty simple I created it just for learning from it.

I have two tables "Categories" and "Subcategories".

I have a field called "CategoryID" in the Subcategories table that is a foreign key , and is related to the first table (categories).

Now what I want to do is, when I delete a row in the "Categories" table, I also want to delete the related rows in the other table (if any exist) at the same time.

I hope I explained the problem accurately, if you need any details please let me know

Thanks in advace,
WassimHi,

You need to delete the rows from the subcategories table first and then delete the corresponding rows from the categories table.

It should be pretty easy as the CategoryID should be in SubCategories table and that the field you need to use to delete rows from the subcategories table.

I hope this helps.

Aash.|||Look at the CASCADE DELETE capability defined when you set the relationship between the two tables. This is inherent and designed to provide for integrity as part of the db structure

Jim|||

Quote:

Originally Posted by Jim Doherty

Look at the CASCADE DELETE capability defined when you set the relationship between the two tables. This is inherent and designed to provide for integrity as part of the db structure

Jim

Hi Jim,

yes that's what I did exactly. That's amazing. if you force your tables to "Cascade Delete", and you delete a row in a parent table, all the rows in the childs tables will also be removed :)

Thanks|||

Quote:

Originally Posted by Tea Maker

Hi Jim,

yes that's what I did exactly. That's amazing. if you force your tables to "Cascade Delete", and you delete a row in a parent table, all the rows in the childs tables will also be removed :)

Thanks

You got it.... glad it helped you

Jim

delete log file

Our server is getting pretty full, the data has already been backed up, but it did not create additional room on the server. Can I delete the log file as long as I have backed up the items I need? We are running Microsoft SQL 2000.
Thanks for the help!Then compress and shrink the log files too

That if you have not done it before should clear up some space...

You do backup to a different drive/ server right?|||Thanks for the response. Yes, we do back up to another drive. Do you know what happens if you erase the log files?|||...Do you know what happens if you erase the log files?The database becomes inaccessible. If you deleted the log file AFTER you detached the database, then you'll have hard time recovering it unless you plan to restore it from the backup. If you deleted it AFTER you stopped SQL service, then upon the next service startup the database will be marked Suspect. This situation can be easily fixed using 1 of the 3 known methods.|||The database becomes inaccessible. If you deleted the log file AFTER you detached the database, then you'll have hard time recovering it unless you plan to restore it from the backup. If you deleted it AFTER you stopped SQL service, then upon the next service startup the database will be marked Suspect. This situation can be easily fixed using 1 of the 3 known methods.

I SUSPECT that you are going to tell him.