Re: Transaction Question

On Thu, 02 May 2002 16:38:15 +0100
mark lonsdale <mark(at)marklonsdale(dot)co(dot)uk> wrote:

> I can see that insert's like
>
> insert into links select max(link_id) + 1 from agent

select max(link_id) + 1 from agent;

This query works correctly.

>
> seem to work okay, but I want to pull in information from multiple
> tables.. I thought something like this would work..
>
> insert into links select max(link_id) + 1, a.agent_id, n.node_id from
> links l, agent a, nodes n ;

select max(link_id) + 1, a.agent_id, n.node_id
from links l, agent a, nodes n ;

But, this one doesn't. It seems to be the reason why the information
couldn't be inserted into "links". For example, you might need to use
GROUP BY clause like the following:

INSERT INTO links
SELECT MAX(a.link_id) + 1, a.agent_id, n.node_id
FROM agent a, nodes n
WHERE a.link_id = n.link_id
GROUP BY a.link_id, a.agent_id, n.node_id;

Regards,
Masaru Sugawara