http://dev.mysql.com/doc/mysql/en/insert-select.html
There are 2 forms of INSERT INTO statement
1. INSERT INTO tbl_name (...) VALUES(...);
2. INSERT INTO tbl_name (...) SELECT ...;
there's no : INSERT INTO tbl_name (...) VALUES(...) SELECT ...;
its either ... or in MySQL i.e. aither provide values or get then form
SELECT ... but not both in same INSERT ...
so indeed:
'INSERT INTO rps_names (nameid, name) VALUES (NULL, "Ryan Smithland")
SELECT LA'
- is invalid query!
Ryan Stille wrote:
I am migrating from MS SQL Server to MySQL 4.1.
I have code in my ColdFusion/SQL Server application that went something
like this:
<cfquery name="insertquery">
SET NOCOUNT ON
INSERT INTO TABLE (....)
SELECT @@IDENTITY AS adid
SET NOCOUNT OFF
</cfquery>
That whole block was processed in coldfusion as a single query. I gave
it a name of "insertquery", and I can get the ID back as
insertquery.adid.
I am having trouble getting the same functionality with MySQL.
<cfquery name="insertquery">
INSERT INTO TABLE (...)
SELECT LAST_INSERT_ID() as adid
</cfquery>
I get this MySQL error:
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 'INSERT
INTO rps_names (nameid, name) VALUES (NULL, "Ryan Smithland") SELECT LA'
at line 2
With MSSQL the trick was the NO COUNT. That told the database server
not to return a count of the rows affected. Is there something similar
I need to do in MySQL to get this to work?
Thanks,
-Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
