SEO

Here is the insert:

INSERT INTO `yohan`.`etf_buy_sell_orders` (portfolio_id, etf_id,
quantity, order_date)
SELECT
portfolio_id,etf_id,quantity,get_previous_etf_trad ing_date(etf_id,transaction_date)
FROM etf_transactions;

And here is the target table:

DROP TABLE IF EXISTS `etf_buy_sell_orders`;
CREATE TABLE `etf_buy_sell_orders` (
id int(10) unsigned NOT NULL auto_increment,
portfolio_id int(10) unsigned NOT NULL,
etf_id int(10) unsigned NOT NULL,
quantity double NOT NULL,
order_date date NOT NULL,
order_execution_date date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

it is obvious why I need to specify the columns. The autoincremented
column will take care of itself, and the column not specified in the
INSERT is allowed to be NULL.

If I try to execute the SELECT statement by itself, it works fine!
However, I get the following error with the INSERT:

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'INSERT INTO `yohan`.`etf_buy_sell_orders` (portfolio_id, etf_id,
quantity, order' at line 1

But in section 12.2.4.1 of the manual, I see this:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

And the example provided immediately after this is:

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Apart from the fact I am not specifying priority, and I do not need to
worry about duplicate keys, I do not see any significant differences
between my INSERT/SELECT statement and that in the manual.

Is this just a bug in MySQL 5.0.45, or is the instruction in the
manual deficient? Or is there an error i have missed?

Thanks

Ted

On Jan 23, 10:45 am, Ted <r.ted.by...@rogers.com> wrote:
> Here is the insert:
>
> INSERT INTO `yohan`.`etf_buy_sell_orders` (portfolio_id, etf_id,
> quantity, order_date)
> SELECT
> portfolio_id,etf_id,quantity,get_previous_etf_trad ing_date(etf_id,transaction_date)
> FROM etf_transactions;
>
> And here is the target table:
>
> DROP TABLE IF EXISTS `etf_buy_sell_orders`;
> CREATE TABLE `etf_buy_sell_orders` (
> id int(10) unsigned NOT NULL auto_increment,
> portfolio_id int(10) unsigned NOT NULL,
> etf_id int(10) unsigned NOT NULL,
> quantity double NOT NULL,
> order_date date NOT NULL,
> order_execution_date date DEFAULT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> it is obvious why I need to specify the columns. The autoincremented
> column will take care of itself, and the column not specified in the
> INSERT is allowed to be NULL.
>
> If I try to execute the SELECT statement by itself, it works fine!
> However, I get the following error with the INSERT:
>
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'INSERT INTO `yohan`.`etf_buy_sell_orders` (portfolio_id, etf_id,
> quantity, order' at line 1
>
> But in section 12.2.4.1 of the manual, I see this:
>
> INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
> [INTO] tbl_name [(col_name,...)]
> SELECT ...
> [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
>
> And the example provided immediately after this is:
>
> INSERT INTO tbl_temp2 (fld_id)
> SELECT tbl_temp1.fld_order_id
> FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
>
> Apart from the fact I am not specifying priority, and I do not need to
> worry about duplicate keys, I do not see any significant differences
> between my INSERT/SELECT statement and that in the manual.
>
> Is this just a bug in MySQL 5.0.45, or is the instruction in the
> manual deficient? Or is there an error i have missed?
>
> Thanks
>
> Ted

Never mind! It is a bug in MySQL Query Browser! When I resort to the
commandline client (mysql), the query is executed without a problem.

Ted

On Wed, 23 Jan 2008 07:50:55 -0800 (PST), Ted wrote:
> Never mind! It is a bug in MySQL Query Browser! When I resort to the
> commandline client (mysql), the query is executed without a problem.

There's also a gotcha in that earlier versions of MySQL don't support
the SELECT .. INTO syntax. Doesn't seem to apply in your case, but
it has trapped others wondering why something worked just fine on a
development system, but failed when trying to roll something out to a
production database.

--
22. No matter how tempted I am with the prospect of unlimited power, I will
not consume any energy field bigger than my head.
--Peter Anspach's list of things to do as an Evil Overlord

On Jan 23, 12:29 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On Wed, 23 Jan 2008 07:50:55 -0800 (PST), Ted wrote:
> > Never mind! It is a bug in MySQL Query Browser! When I resort to the
> > commandline client (mysql), the query is executed without a problem.
>
> There's also a gotcha in that earlier versions of MySQL don't support
> the SELECT .. INTO syntax. Doesn't seem to apply in your case, but
> it has trapped others wondering why something worked just fine on a
> development system, but failed when trying to roll something out to a
> production database.
>
> --
> 22. No matter how tempted I am with the prospect of unlimited power, I will
> not consume any energy field bigger than my head.
> --Peter Anspach's list of things to do as an Evil Overlord

Yes, I got bit on that one. I am developing using version 5.0.45, and
until last week my colleague was working with 5.0.22, and scripts that
worked fine on my system failed on his. Since he answers to me, I
told him to upgrade his system to the latest version, and now all is
fine, at least until I push the capabilities of MySQL again. ;-)
We're now at least working on the exact same version.

Thanks

Ted