On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote: > There's an awesome feature that was added to PostgreSQL a while back called > RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement > behave like a SELECT statement. You can do something like this: > > INSERT INTO mytable (id, value) > VALUES (1, 'something') > RETURNING any_column_you_want; > > This would be equivalent to running something like this in MySQL: > > INSERT INTO mytable (id, value) > VALUES (1, 'something'); > > SELECT any_column_you_want > FROM mytable > WHERE id = 1; > > Here is another example with an UPDATE query: > > UPDATE mytable SET > value = 'something' > WHERE id = 1 > RETURNING id, other_number; > > The nice thing about this is that every insert or update can return any > column you want (even multiple columns) without having to do the > INSERT/UPDATE then turn around and perform another SELECT query. > > I want to use this because when I insert a value into a table, I don't > always want to get the primary key returned to me. Sometimes I want another > column which may contain a candidate key and I'd like to avoid the > round-trip and additional logic incurred with running multiple queries. > > Does RETURNING exist in any current release of MySQL or is it on the TODO > list even? If it's not, how can I go about asking to have it put on there? > > -- Dante > > ---------- > D. Dante Lorenso > [EMAIL PROTECTED]
You can do your insert through a stored procedure and then at the end do a select of those values. http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14 "22.4.14: Can MySQL 5.0 stored routines return result sets? Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or above) client-server protocol for this to work. This means that — for instance — in PHP, you need to use the mysqli extension rather than the old mysql extension. " -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
