SubQuery bug in 4.1

am 04.08.2003 17:09:30 von Daniel Kiss

Hi all,

I have two tables

CREATE TABLE main (
ID int not null,
Value int
);

CREATE TABLE sub (
mainID int not null,
KeyDate date not null,
SubValue int not null
);

I want the Value field in the main table to be set to the latest SubValue
in the sub table.
I suppose this syntax should work. But it does not, and sets the Value
fields to incorrect values.

update main set Value = (select SubValue from sub where main.ID =
sub.mainID order by KeyDate desc limit 1)

Any ideas?

Thanks,
Dan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: SubQuery bug in 4.1

am 04.08.2003 17:16:49 von Mark Hedges

Surely this will just work?

update main,sub set main.Value = sub.subValue where main.id=sub.mainid;

Or have I misunderstood what you are wanting?

--
Mark

----- Original Message -----
From: "Daniel Kiss"
To: ;
Sent: Monday, August 04, 2003 4:09 PM
Subject: SubQuery bug in 4.1

> Hi all,
>
> I have two tables
>
>
> CREATE TABLE main (
> ID int not null,
> Value int
> );
>
> CREATE TABLE sub (
> mainID int not null,
> KeyDate date not null,
> SubValue int not null
> );
>
>
> I want the Value field in the main table to be set to the latest SubValue
> in the sub table.
> I suppose this syntax should work. But it does not, and sets the Value
> fields to incorrect values.
>
> update main set Value = (select SubValue from sub where main.ID =
> sub.mainID order by KeyDate desc limit 1)
>
> Any ideas?
>
> Thanks,
> Dan
>
>
>
> --
> MySQL Bugs Mailing List
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe: http://lists.mysql.com/bugs?unsub=mark@wzc.org.uk

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: SubQuery bug in 4.1

am 04.08.2003 17:16:49 von Mark Hedges

Re: SubQuery bug in 4.1

am 04.08.2003 17:19:16 von Sinisa Milivojevic

Daniel Kiss writes:
> Hi all,
>
> I have two tables
>
>
> CREATE TABLE main (
> ID int not null,
> Value int
> );
>
> CREATE TABLE sub (
> mainID int not null,
> KeyDate date not null,
> SubValue int not null
> );
>
>
> I want the Value field in the main table to be set to the latest SubValue
> in the sub table.
> I suppose this syntax should work. But it does not, and sets the Value
> fields to incorrect values.
>
> update main set Value = (select SubValue from sub where main.ID =
> sub.mainID order by KeyDate desc limit 1)
>
> Any ideas?
>
> Thanks,
> Dan
>

Can you try (with 4.1.1 from our BK tree):

update main set Value = (select max(SubValue) from sub);

There should also be a WHERE clause for update statement or all rows
will be updated.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: SubQuery bug in 4.1

am 04.08.2003 17:19:16 von Sinisa Milivojevic

MySQL extension

am 04.08.2003 17:27:17 von Primaria Falticeni

Hello,

Would you help me with some samples for making a MySQL extension in C/C++?
Please!

Thanks Anticipated,
Iulian

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: SubQuery bug in 4.1

am 04.08.2003 17:31:55 von Gerald Clark

Your primary query has no where clause, so you are setting all Value to
a value from a random record for the most recent date.
Hardly looks like a 'bug' to me.

And why the cross post?

Daniel Kiss wrote:

> Hi all,
>
> I have two tables
>
>
> CREATE TABLE main (
> ID int not null,
> Value int
> );
>
> CREATE TABLE sub (
> mainID int not null,
> KeyDate date not null,
> SubValue int not null
> );
>
>
> I want the Value field in the main table to be set to the latest
> SubValue in the sub table.
> I suppose this syntax should work. But it does not, and sets the Value
> fields to incorrect values.
>
> update main set Value = (select SubValue from sub where main.ID =
> sub.mainID order by KeyDate desc limit 1)
>
> Any ideas?
>
> Thanks,
> Dan
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: SubQuery bug in 4.1

am 04.08.2003 17:31:55 von Gerald Clark