I'm trying to parameterize the following insert with a nested select.
INSERT IGNORE INTO table1 (creation_timestamp, str1, str2)
(SELECT now(), "param1", str2 FROM table2 WHERE key = "param2");
I'd like something like
INSERT IGNORE INTO table1 (creation_timestamp, str1, str2)
(SELECT now(), ?, str2 FROM table2 WHERE key = ?)
VALUES ("param1", "param2");
Anyone know how I can accomplish something like this?
Not exactly the same, but very similar.
You can use prepared statements: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
Example:
PREPARE soExample
FROM 'INSERT
INTO usr (id, username, profile_pic)
VALUES (NULL, ?, (SELECT name
FROM customers
WHERE id = ?
LIMIT 1))';
SET @uname = "someUserNameForTheExample";
SET @id = "1";
EXECUTE soExample USING @uname, @id;
Or you can user procedure or/and functions as well
FUNCTION
DROP FUNCTION IF EXISTS insertExample$$
CREATE FUNCTION insertExample(userNameVar VARCHAR(255), uID INT(11)) RETURNS BOOLEAN
BEGIN
INSERT
INTO usr (id, username, profile_pic)
VALUES (NULL, userNameVar, (SELECT name
FROM customers
WHERE id = uID
LIMIT 1));
IF ROW_COUNT() > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END$$
FUNCTION USE
SELECT insertExample("SomeUsername" 2);
Perhaps you should start by reading https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html. As Mysql Functions enables parameterized input for pre-build queries.
