Re: SQL Q's.

[Jason Pincin <jpincin@linuxkb.org>]

I don't know of any good sql sites off the top of my head - do you dan?

But I'll answer yur questions below:


> 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?

The primary key is simply an index.  It would be just as fast to select
from any other indexed field.  You wouldn't want to use the primary key to
select info if you didn't know the primary key :).  If you wanted someone
to be able to pull up info based on email address or sumthin... in which
case you'd select by email address :).  Which would prolly be a
non-indexed query - but they ARE allowed too :).

> 2) How to you write a SQL statement to select the fields from rows in a
> table based on the value of another field in the table.  Ie:
> SELECT CategoryID,CategoryName,Description FROM Categories WHERE Approved
> = 'Y'

Now sure what you mean by this - you seemed to have answered your own
question because that would work as long as Approved exists in the table
your selecting from.

> 3) How do I insert a row in a table for which the primary key is
> auto-incrementing or not auto-inc?

If you're inserting into a table with an auto-incrementing field, in MySQL
you just insert a NULL into the position of the field.  For example, if
you have a table with 3 fields: x, y, x - where x is auto-incrementing you
would just do the following:

insert into table_test values ('', 'this is an', 'example');

> 3) How do I update one or more fields in a row?

Using the above table example you'd do this:

UPDATE table_test SET y = 'this is', z = 'changed now' where x = 2;

IMPORTANT - if x is not unique it will update anything that has x = 2.  If
you don't provide a WHERE at all, all y's and z's in the table will be set
to the new values - not good.

> 4) How do I update one field in multiple rows based on a value of a field
> in that row?

You could do the following:

UPDATE table_test SET y = 'mass update' where z = 'value I am testing
for';



Anything else?

Jason