Thread: Transaction Question

Hi

Im a newbie to this but am trying to set up a transaction that will
insert a new row into a table based on values in other tables ( multiple
tables )

I can see that insert's like

 insert into links select max(link_id) + 1 from agent

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 ;

but it doesn't seem to..

When I used to use sybase a few years back I remember being able to do
something like

select @variable=field fom table
insert into newtable values(@variable)

Is there any similar variable substitution I can do in postgresql to do
the same thing.. Ideally I need my code to also work on other rdms's as
well so it would have to be a standard sql feature.

Also, is there any way to do conditional statements from within a
transactinon e.g. if statements ?

Thanks

Mark

On Thu, 02 May 2002 16:38:15 +0100
mark lonsdale <mark@marklonsdale.co.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