Re: SQL NEWBEE QUESTION
- From: "Maarten Wiltink" <maarten@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 27 Oct 2006 16:45:16 +0200
"Richard Wakeman" <jrw16@xxxxxxxxxxxx> wrote in message
news:NLSdnZYrxYRGl9_YnZ2dnUVZ_tOdnZ2d@xxxxxxxxxxxxxxx
----+----*----+----*----+----*----+----*----+----*----+----*----+----*----+-
---*
[...]
What I need to have happen is to have a quick way (SQL) to go thru
the History table, and replace some data into a History Table Field
based upon whether that History records's counter part in the Main
Table has been deleted (not there anymore). We still want to keep
History Records of those deleted in the Main. Am I making any sense?
Apart from asking this in a Delphi newsgroup, yes, clear enough.
Anyway, the SQL code for it would be something like:
UPDATE HISTORY.DBF SET HDUPNUM = "1" INNER JOIN MAIN -------
and here I get lost....
You can't use an INNER JOIN, the records that _don't_ have a matching
Main record would fall out. So let's make it a LEFT JOIN. The resulting
dataset will have History records in every row, and the matching Main
row if there is one, NULLs otherwise.
You want to be able to recognise the History records that don't have
matching Main records, well, just look at any of the NULLs. If you
look at a field that can't otherwise be null, the primary key for
example, you know it's because of the entire record having gone missing.
UPDATE
History
LEFT JOIN Main
ON (History.PrimaryKey = Main.PrimaryKey)
SET
History.OrphanedFlag = 1
WHERE
(Main.PrimaryKey IS NULL);
Groetjes,
Maarten Wiltink
.
- References:
- SQL NEWBEE QUESTION
- From: Richard Wakeman
- SQL NEWBEE QUESTION
- Prev by Date: Re: SQL NEWBEE QUESTION
- Next by Date: Re: Best way to drag and drop ?
- Previous by thread: Re: SQL NEWBEE QUESTION
- Next by thread: Best way to drag and drop ?
- Index(es):
Relevant Pages
|
