MySQL questions

[Steve Holden]

[Billy: Hope you don't mind me copying this reply to the list]

----- Original Message -----
From: "Billy Ng" <kwokng at earthlink.net>
To: "Steve Holden" <sholden at holdenweb.com>
Sent: Tuesday, May 07, 2002 7:44 PM
Subject: Re: MySQL questions


> > > 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)   |      |     |                     |
|
>
The problem here is that your freq column isn't in any form that SQL can use
to add to a datetime. About your only option appears to be to read the row
or rows you want to update into your program, and do the arithmetic you want
there (perhaps with the mxDateTime package).

If you turned freq into an integer field holding the number of seconds
between updates you could do this much more easily in pure SQL:

mysql> SELECT * FROM tmp;
+---+---------------------+------+
| k | d                   | i    |
+---+---------------------+------+
| 1 | 2002-05-11 20:58:15 |  900 |
| 2 | 2002-05-11 13:15:00 | 7200 |
+---+---------------------+------+
2 rows in set (0.00 sec)

mysql> UPDATE tmp SET d=d+i WHERE k=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> SELECT * FROM tmp;
+---+---------------------+------+
| k | d                   | i    |
+---+---------------------+------+
| 1 | 0000-00-00 00:00:00 |  900 |
| 2 | 2002-05-11 13:15:00 | 7200 |
+---+---------------------+------+
2 rows in set (0.00 sec)
>
> > > 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.
>

As far as I know there is no way in SQL to do this optional insert.

> > > 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.
>
But don't forget that nested queries, or queries using joins, will almost
always be quicker even though the SQL is more complex.

regards
 Steve



Steve Holden: http://www.holdenweb.com/ ; Python Web Programming:
http://pydish.holdenweb.com/pwp/