Insert copy auto_increment to another column

[zb]

I am coming from MS SQL Server background. And too used to SPs. Here is
my question-
I want to insert data into a table and one of the columns is a copy of
the primary key which is an auto increment field. How can I do this
without running additional Update query?

Eg. INSERT INTO maddress ( m_id, f_name, l_name ) VALUES ([this is the
new id assigned to this row, MAX(id)+1 failed for me], 'fname',
'lname');

I tried using Last_insert_id (expr) but every time it inserts 1.

I am using C# with MySQL.

I also tried concating:
SELECT @last := LAST_INSERT_ID();
UPDATE mytable SET m_id=@last where id=@last;

to the Insert statement. I got this error:

ERROR [23000] 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 ';SELECT @lastid := LAST_INSERT_ID(); UPDATE maddress SET m_id
= @lastid WHE' at line 1

This is the ODBC class where it fails:
System.Data.Odbc.OdbcCommand.ExecuteNonQuery()

Here is the schema.

CREATE TABLE `maddress` (
`ID` bigint(20) unsigned NOT NULL auto_increment,
`m_ID` bigint(20) NOT NULL default '0',
`fname` varchar(75) ,
`lname` varchar(75) ,
`description` text,
`Created_By` varchar(75) default NULL,
`Created_Date` datetime default NULL,
`Modified_By` varchar(75) default NULL,
`Modified_Date` datetime default NULL,
PRIMARY KEY(`ID`,`m_ID`),
FULLTEXT KEY `AddressFulltext` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED


Also, my fulltext does not work.

 

[Val Mazur \(MVP\)]

Hi,

In a case of SQL Server 200 just use SCOPE_IDENTITY() function right after
INSERT statement.

SELECT @last = SCOPE_IDENTITY()

 

[zb]

Thanks Val, I come from MS SQL background. But on this project I am
using MySQL. Can you help me there?

-Zeya.

 

[Val Mazur \(MVP\)]

I do not know MySql, but I believe it should have similar function or some
sort of global variable like @@IDENTITY in SQL Server