-1

I want to do something like this (pseudocode):

SELECT IF (I don't get NULL, "pass", null)
FROM(
INSERT INTO table VALUES (value)
)

If the insert is successful, it will not return anything, so this query returns null. If the insert is successful, I will get "pass" instead of a value.

I don't know how to do this. I'm thinking a stored procedure can do it, but I also think that it's not necessary.

Edit: I've so far tried this, though:

INSERT INTO `permissions` VALUES (7,"test",null);

SELECT IF(`p_id` IS NOT NULL, "pass", null ) `p_id`
FROM `permissions`
WHERE `p_id` = 7;

Where 7 is user-defined. This one works as I want it to. It's not a stored procedure, but it's two queries. I want to know if it's possible to do this with just one query?

markovchain
  • 515
  • 1
  • 10
  • 25

2 Answers2

2

A MySQL INSERT statement doesn't return a resultset; so it can't be used a row source for a query. If the INSERT statement fails, it will throw an error.

To return a resultset, a separate SELECT statement would need to be executed.

These two statements could be executed in the context of a stored procedure, if the goal is to execute a single database call.

EDIT:

Q: I want to know if it's possible to do this with just one query?

A: No, it's not possible with just one statement. You'd need two statements: an INSERT statement (to perform the insert operation) and an SELECT statement (to return a result).

To do this in a single database call, you'd need a MySQL stored program that performs the two statements: first, the INSERT, and catch errors thrown (with a CONTINUE HANDLER), and then a SELECT to return a resultset.

It's not clear why you need a resultset that returns a column containing 'PASS' or NULL.

The normative pattern would be to run the INSERT statement, check to see whether it threw an error, and/or check "affected rows" using function available in the client library, and/or run a separate SELECT statement to retrieve the value from the MySQL ROW_COUNT() function, e.g.

INSERT INTO mytable (mycol) VALUES ('myval') ;
SELECT IF(ROW_COUNT(),'PASS',NULL) ;

(To do this within a stored procedure, you'd need to define a CONTINUE HANDLER in the procedure to "catch" errors from the INSERT so that the procedure will continue running (rather than throwing an error) so the SELECT statement will be run.)

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Can I write a stored procedure that throws "pass" if the insert is successful, or null if not? (obviously, yes, but I mean, elegantly) – markovchain Jul 29 '14 at 13:41
0

Is this what you wanted ??

INSERT INTO table1
(
 column1,
 column2,
)
VALUES
(
 (
   SELECT column
   FROM table2 
   JOIN table3 ON condition
 ),

 (
  "a string"
 ),
);

See the link here

Community
  • 1
  • 1
Avinash Babu
  • 6,171
  • 3
  • 21
  • 26
  • This replicates one table's data into another, right? I just want to throw a "pass" result if the preceding insert statement was successful. – markovchain Jul 29 '14 at 13:39