Re: deleting rows based on other tables

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Herbie (Herbie_at_discussions.microsoft.com)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 12:17:03 -0700

Well, I guest you just totally misunderstood a very simple question. Simple
questions require simple answers.

Not knew to Database or Structured Query Language. Only just started using
T-SQL.

so next time I don't need the bible. Just an simple answer to my question.

"Joe Celko" wrote:

> >> I am just starting out in T-SQL. <<
>
> Build good habits now! The first good habit is in the note at the
> bottom of this posting. We let a newbie slip once, then we fry him.
>
> Records are not rows, columns are not fields and tables are files --
> TOTALLY different concepts. Among these differences, a column in one
> table can reference a column in a second table. We call these the
> referenced and referencing tables. The terms "child" and "parent" are
> from IMS or other network databases and imply one directional pointer
> chains. We do everything in SQL with keys, which you did not show us.
>
> Is the common key one column? or 14 columns? Did you get the orphans
> because of problems with NULLs? I can think of about six more questions
> that some DDL would answered It is bitch to write code without seeing
> the data or having specs.
>
> But with a one-column key, you can write:
>
> DELETE FROM TableA
> WHERE NOT EXISTS
> (SELECT *
> FROM TableA
> WHERE TableA.somekey = TableB.somekey);
>
> While you are building good habits, notice that the Standard SQL syntax:
>
> 1) Does not allow the DELETE FROM clause to be abbreviated to DELETE.
>
> 2) Does not allow an alias in the DELETE FROM clause.
>
> 3) Does not have a separate FROM clause for creating some kind of
> working table. That proprietary syntax is a total nightmare for a lot
> of reasons that I will skip.
>
> Now, that we have mopped the floor, it is time to fix the leak. Add DRI
> actions to your referencing table so the SQL engine will clean up the
> relationships for you:
>
> CREATE TABLE TableA
> (somekey INTEGER NOT NULL PRIMARY KEY,
> ...);
>
> CREATE TABLE TableA
> (...
> somekey INTEGER NOT NULL
> REFERENCES TableA(somekey)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> ..);
>
> --CELKO--
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

Relevant Pages

  • Re: deleting rows based on other tables
    ... I guest you just totally misunderstood a very simple question. ... > Not knew to Database or Structured Query Language. ... People that don't have a thorough understanding of SQL usually think ... We do everything in SQL with keys, which you did not show us. ...
    (microsoft.public.sqlserver.programming)
  • Re: Key attributes with list values was Re: What are the differences ...KEY
    ... Jane Harper is married. ... And a constraint that states that single people cannot become divorced. ... database, or users, for that matter, to distinguish between them. ... That's the whole point of keys. ...
    (comp.databases.theory)
  • Re: Pin generation algorithm question
    ... the keys would be a big ... Suppose that we have a database that contains all valid numbers, ... load among several servers that all need access to this database. ... So the only real problem is which systems are accessing this crypto box. ...
    (sci.crypt)
  • Re: Database design, Keys and some other things
    ... >> Or 'the database has no opinion as what Donald Trump's e-mail address might ... some keys can be wrong or a data can ... Meaning is not related to just one number. ... > is concerned a VIN is not a surrogate key, ...
    (comp.databases.theory)
  • Re: Key attributes with list values was Re: What are the differences ...KEY
    ... database, or users, for that matter, to distinguish between them. ... That's the whole point of keys. ... But that is true of any constraint. ... keys can change, then either updates must be singular, that is, must affect ...
    (comp.databases.theory)