One annoying aspect of SQL is that sometimes you really want to get some result out of an INSERT or UPDATE statement. But in MySQL, you can. It’s just a nifty construct.
First just an UPDATE:
UPDATE tbl SET col = (@var := col), bla=value WHERE ...
SELECT @var
So, you can assign a column to itself (MySQL does a read before write, for updates), but also assign that value to a server-side variable which you can then retrieve. That’s how you get data out of a row you’re updating!
Now, how about the case where you need to insert something into one table, but regardless of whether or not it already existed you need to insert or update info into a second table (and also the id from the first table).
That’s your christmas challenge from me…. no selects or transactions necessary. Good luck!
Given this table, with the following contents
Here is an INSERT statement that will also record what it is inserted:
Merry Christmas!
I think we have at least one more possibility.
Assuming we have a table with an id-column (auto_increment, primary key) and one column called “Name”, which is indexed by a unique key.
1)
2)
Greetings,
Anse
Well, ‘RETURNING’ clause has been used for years on real RDBMS.
Take a look at it; MySQL newbies are always excited about ‘reinventing’ the wheel every week :).
No need to bitch, it says more about you and I’m sure that’s not what you intended.
We can all exchange ideas and debate like good grownups.
The RETURNING clause on its own actually does not accomplish the functionality required by the question. What RETURNING does is return the information that was actually inserted, it does not deal with any information should the row already exist.