Shawn's rewrite is much easier to read. But how about this for the two
statements. Just type it all out as two separate queries separated by a
semicolon. Then it becomes a batch command which runs just fine. I
like to run huge batch commands.
Jordan Sparks
INSERT fun_informations
SELECT conn.INT_InternalCallId,
'CID',
'AAAAAA',
1,
null
FROM cti_connections conn
LEFT JOIN app_ivrtable ivr
on conn.chr_device=ivr.chr_device
WHERE conn.int_state = 7
and ivr.CHR_Machine = 'm1'
and ivr.INT_IVRLine = '1';
INSERT fun_informations
SELECT conn.INT_InternalCallId,
'CPN',
'BBBBB',
1,
null
FROM cti_connections conn
LEFT JOIN app_ivrtable ivr
ON conn.chr_device=ivr.chr_device
WHERE conn.int_state = 7
and ivr.CHR_Machine = 'm1'
and ivr.INT_IVRLine = '1';
-----Original Message-----
From: SGreen@stripped [mailto:SGreen@stripped]
Sent: Wednesday, September 22, 2004 6:19 AM
To: Daniel Bronzeri Barbosa
Cc: win32@stripped
Subject: Re: HELP with "INSERT SELECT"
I have two suggestions:
First - drop those parentheses when doing a single INSERT ... SELECT ...
(http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html)
INSERT fun_informations
SELECT conn.INT_InternalCallId,
'CID',
'AAAAAA',
1,
null
FROM cti_connections conn
LEFT JOIN app_ivrtable ivr
ON conn.chr_device=ivr.chr_device
WHERE conn.int_state = 7
and ivr.CHR_Machine = 'm1'
and ivr.INT_IVRLine = '1'
Second - try a UNION to chain your queries together.
(http://dev.mysql.com/doc/mysql/en/UNION.html)
INSERT fun_informations
SELECT conn.INT_InternalCallId,
'CID',
'AAAAAA',
1,
null
FROM cti_connections conn
LEFT JOIN app_ivrtable ivr
on conn.chr_device=ivr.chr_device
WHERE conn.int_state = 7
and ivr.CHR_Machine = 'm1'
and ivr.INT_IVRLine = '1'
UNION
SELECT conn.INT_InternalCallId,
'CPN',
'BBBBB',
1,
null
FROM cti_connections conn
LEFT JOIN app_ivrtable ivr
ON conn.chr_device=ivr.chr_device
WHERE conn.int_state = 7
and ivr.CHR_Machine = 'm1'
and ivr.INT_IVRLine = '1'
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Daniel Bronzeri Barbosa" <daniel@stripped> wrote on 09/22/2004
08:01:59 AM:
> How I can Insert 2 records with a Insert Select?
>
> The SQl Clause that I am using is this:
>
> Insert into fun_informations
> (Select conn.INT_InternalCallId as INT_InternalCallId,
> 'CID' as CHR_PropName,
> 'AAAAAA' as CHR_PropValue,
> 1 as INT_Updated,
> null as INT_Key
> from cti_connections conn left join app_ivrtable ivr
> on conn.chr_device=ivr.chr_device
> where conn.int_state = 7 and
> ivr.CHR_Machine = 'm1'
> and ivr.INT_IVRLine = '1')
>
> but I wanted to insert given of this clause
>
> Insert into fun_informations
> (Select conn.INT_InternalCallId as INT_InternalCallId,
> 'CID' as CHR_PropName,
> 'AAAAAA' as CHR_PropValue,
> 1 as INT_Updated,
> null as INT_Key
> from cti_connections conn left join app_ivrtable ivr
> on conn.chr_device=ivr.chr_device
> where conn.int_state = 7 and
> ivr.CHR_Machine = 'm1'
> and ivr.INT_IVRLine = '1'),
> (Select conn.INT_InternalCallId as INT_InternalCallId,
> 'CPN' as CHR_PropName,
> 'BBBBB' as CHR_PropValue,
> 1 as INT_Updated,
> null as INT_Key
> from cti_connections conn left join app_ivrtable ivr
> on conn.chr_device=ivr.chr_device
> where conn.int_state = 7 and
> ivr.CHR_Machine = 'm1'
> and ivr.INT_IVRLine = '1')
>
>
> It is possible? If not how i can do this?
>
> Tks,
>
> Daniel
