1 {{ upvoteCount | shortNum }}
1 {{ upvoteCount | shortNum }}

"Super-Temporary" table ability in MySQL?

by David Beroff
1 replies
I'd like to be able to specify small tabular data "on-the-fly" in a single MySQL statement, but can't figure out how. Here's a (very simplified) example of how I currently use a two-by-two temporary table; the real-life statements are far more complex, of course:

Code:
CREATE TEMPORARY TABLE Pair (Act VARCHAR(8), Dir INT SIGNED);

INSERT INTO Pair (Act, Dir) VALUES ("Realized", 1), ("Unreal", -1);

INSERT INTO Transaction (AccountID, Amount, ActivityID)
SELECT @AccountID, @Amount * Dir, Act FROM Pair;

DROP TEMPORARY TABLE Pair;
I want to be able to do something like this:

Code:
INSERT INTO Transaction (AccountID, Amount, ActivityID)
SELECT @ThisAcctID, @TransAmt * Dir, Act FROM 
VALUES ("Realized", 1), ("Unreal", -1) AS Pair;
Any ideas? Thanks!
#ability #mysql #statement #supertemporary #table #temporary
  • Profile picture of the author David Beroff
    In case anyone else stumbles on this post, I found a way:

    Code:
    INSERT INTO Transaction (AccountID, Amount, ActivityID)
SELECT @ThisAcctID, @TransAmt * Dir, Act FROM 
(SELECT "Realized" AS Act, 1 AS Dir UNION SELECT "Unreal", -1)
AS Pair;
    Signature
    Put MY voice on YOUR video: AwesomeAmericanAudio.com
    {{ DiscussionBoard.errors[10438595].message }}

Trending Topics