Re: SQL Q's.

["Pete St. Onge" <pete@seul.org>]

>> 1)  When selecting a row for updating, the quickest way to select
>>     it is by the primary key.  True?  Are there times when you
>>     wouldn't want to use the primary key?
> I would assume that to be the case, yeah.  Jason would probably
> answer this better than I.

Yeah, that can work, but it depends on what you're doing it in the first
place; if you already know what records have which primary key then by
all means. But if there is a chance that the underlying table could be
updated between the time you know this and the time you modify your
records, you could miss updating / selecting all of the appropriate
records. (Jason?)

If you have a lot of records to modify, it's probably more accurate to
UPDATE them using a broader SQL statement than modifying individual
records, particularly if there are a lot to do.

If your data has "structure" (data where individual records are not
completely independant of each other), however, you may not have the
option and would be forced to examine records on an individual basis. 

What I mean by "structure": my temperature and oxygen profile data are
collected at regular depth intervals in the lake; the entity is the
profile, but an instance of a profile has a series of records (oxygen,
temperature at a series of depths in the lake) and thus profile data
only has value if it has more than one record for that instance. Put
another way: one or two spot temperature measurements do not make a
profile*; a bunch of regularly-spaced measurements do.

To carry out any analysis of that profile, I have to look at a sequence
of records, and not individual records. I can't really do that using
SQL, so I have to step through the records in question if I want to
carry out particular analyses (maximum rate of temperature change,
f'r'instance).

* Not quite true in all cases, but I won't go into it. :)


>> 3) How do I insert a row in a table for which the primary key is
>>    auto-incrementing or not auto-inc?
> When it auto-incs, I believe you can just leave that field out of
> your list.

That's my understanding as well.

As for adding rows, you can do it via SQL:

INSERT INTO Categories (CategoryName,Description) VALUES ('Fish' ,
'Aquatic Animal' );

This is kinda clunky if you have a lot of records to add, though. 
You can also insert records from one table into another

INSERT INTO Categories (CategoryName,Description)
SELECT CategoryName,Description from NewCategories;

In either case, I'm pretty sure that fields not explicitly qualified are
given their default or null values.


>> 3) How do I update one or more fields in a row?
> 
> UPDATE tablename SET fieldname = 'value'
> 
> That will set that field in EVERY record.  Add the WHERE clause
> just like in SELECT to specify which rows to update.

If memory serves, you will probably have to run an UPDATE query for
every different field that you want to update, however. (Jason?)

>> 5) Anyone know any good SQL sites?  Something like SQL for Dummies?
> Not really...  I just use a college SQL textbook.  A flimsy one at
> that.  I know that there's an SQL Unleashed that looks *somewhat*
> cool...
     Heh, you guys are gonna shoot me. I still use the SQL help file
from Access. :) I have seen a few SQL books, but nothing stands out.
That could very well mean that any book you get would be a good bet,
Aaron.

Hope this helps,

Pete

-- 
Pete St. Onge
pete@seul.org