SQL delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL delete

Author

Topic

NickJ
Starting Member

2 Posts

Posted - 2005-03-22 : 14:09:49

I am trying to delete the resource_id associated with 5 bridge table from the parent table. The only id I can use is the title_id in the Parent table. Here is what I wrote and I received an ORA-00933 error after en_esrc_resources on the first line. Suggestions to remedy this code?

DELETE en_esrc_resources.resource_id, en_esrc_type.resource_id, en_esrc_region.resource_id,
en_esrc_grade.resource_id, en_esrc_category.resource_id, en_esrc_link.resource_id
FROM en_esrc_resources, en_esrc_type, en_esrc_region, en_esrc_grade, en_esrc_category, en_esrc_link
WHERE title_ID = 2783
AND (en_esrc_resources.resource_id = en_esrc_type.resource_id
OR en_esrc_resources.resource_id = en_esrc_region.resource_id
OR en_esrc_resources.resource_id = en_esrc_grade.resource_id
OR en_esrc_resources.resource_id = en_esrc_category.resource_id
OR en_esrc_resources.resource_id = en_esrc_link.resource_id);

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-22 : 14:11:20

You can only delete from 1 table at a time....

You could establish CASCADING DELETE to help you....

Brett

8-)

NickJ
Starting Member

2 Posts

Posted - 2005-03-22 : 14:15:01

Would CAscading Delete drop the row instead of the record?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-22 : 16:16:11

quote:
Originally posted by NickJ

Would CAscading Delete drop the row instead of the record?

what is the difference ?

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-22 : 16:25:27

I don't know how to answer tht one....

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-22 : 16:29:07

First, a row is a record. They are synonyms. We use the word row for SQL Server. Please explain further what you mean.

Second, you need to delete all child data before you can delete the parent. Simple parent delete example that has one child:

DELETE c2
FROM Child2 c2
INNER JOIN Parent p
ON c2.SomeColumn = p.SomeColumn
WHERE p.SomeOtherColumn = 'SomeValue'

DELETE
FROM Parent
WHERE SomeOtherColumn = 'SomeValue'

Also, keep in mind that this is a SQL Server site and not an Oracle site. It shouldn't matter with this topic, but it's something that you need to be aware of.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-23 : 10:00:47

quote:
Originally posted by tduggan

First, a row is a record. They are synonyms.

Really?

I wouldn't say so. A row is not a record, nor is a column a field.

To me they infer a flat file of data, not data in a relational database.

But that's just me being pedantic again....

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 12:22:33

I believe these terms are used in Access, which doesn't consist of flat files.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources