MySQL questions

[Billy Ng]

> > Question 1
> > I have a freq field that contains '"15:0" MINUTE_SECOND', why can't I do
> > this?
> >
> > UPDATE table SET set next_update=DATE_ADD(NOW(), INTERVAL table.freq);
> >
> Perhaps if you explained what it was you were trying to do, then we might
be
> able to explain how to do it in SQL and/or Python. Seems like you want to
> add an interval specified in hours and minutes (as a string?) in a row of
> one table to a field in a row of some other table.
>
> Is "freq" in the same table as "next_update"? Is there just one row in
this
> table?
>
> If the table rows aren't huge, maybe you cxould even give us their
> definition (from the "DESCRIBE table" SQL command).

Each row has its own freq.  "freq" is in the same table as "next_update".
There are many rows in the table.

| last_update  | timestamp(14) | YES  |     | NULL                |       |
| next_update  | datetime      |      |     | 0000-00-00 00:00:00 |       |
| xml_text     | text          |      |     |                     |       |
| submit_date  | datetime      |      |     | 0000-00-00 00:00:00 |       |
| assistant_id | int(11)       |      |     | 0                   |       |
| search_id    | varchar(25)   |      | PRI |                     |       |
| freq         | varchar(30)   |      |     |                     |       |


> > Question2
> > How can I INSERT a record into the table if the table does not have any
> > record with a provided id in the id field?  Do I need to join a SELECT
> > statement?
> >
> You mean the table has no primary key? That would make it rather difficult
> to uniquely specify (with a WHERE clause) which row to update. The usual
way
> to get around this is to use an INTEGER AUTO_INCREMENT field as the
primary
> key. When you insert into a table with such a key, but provide no value
for
> the key field (or specifically provide a NULL value) MySQL puts a unique
> value for that field in the inserted row. You can retrieve the inserted
> value in SQL using the last_inserted() or some similar function whose name
> escapes me for the moment.

The combination of the user_id and product_id will be uniqe.  When I get the
product_id and user_id, i want to change the tableA to see if there is a
record that has this user_id and product_id.  If not, I will insert a record
with this user_id and product_id.  What I am doing right is:

Query to count tableA whether it has the user_id and product_id
if count is 0
insert tableA (user_id, product_id) values (xxxxx, xxxxxxx)

I think it must a way to write it in one SQL statement.

> > Question3
> > Can I query from a query in MySQL?
> >
> You mean can the output of one query become the input of another? Only if
> you store the first query's output in a temporary table.
>
> Most often, however, this isn't necessary if you make your SQL queries a
> little more complex. Again, a few more details might help...

Oh yes, dummy table will do the job.  I have not done databases for too
long.  I totally forgot this.

Thanks!

Billy Ng