how to create database with subquery's return value name?

Question

I want to create database using subquery's return value name

CREATE DATABASE (SELECT (REPLACE( REPLACE( REPLACE( standard_name, ' ', '' ), '-', '' ), '_', '' )) FROM standard_master WHERE _id = 1)

My Subquery return value

SELECT (REPLACE( REPLACE( REPLACE( standard_name, ' ', '' ), '-', '' ), '_', '' )) FROM standard_master WHERE _id = 1

standard01

Create database query give me an syntax error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT (REPLACE( REPLACE( REPLACE( standard_name, ' ', '' ), '-', '' ), '_', ''' at line 1

What is your question about, do you really mean database and if so what isn't working? — Joakim Danielson, Aug 22 '18 at 11:59
Question edited. now you understand my question @JoakimDanielson — Vishal Bareja, Aug 22 '18 at 12:04
You cannot do this using a subquery (the database name has to be a string literal), you need to use [dynamic sql](https://stackoverflow.com/q/14379088/6248528) for this. — Solarflare, Aug 22 '18 at 12:09
Why Negative vote? If you didnt understand anything in question then tell me what can i do for you. Is My quetion worng? — Vishal Bareja, Aug 22 '18 at 12:10
You need to build a string containing your sql code and execute it, see https://stackoverflow.com/questions/999200/is-it-possible-to-execute-a-string-in-mysql — Joakim Danielson, Aug 22 '18 at 12:18
Thank you @JoakimDanielson for your time. it's resolved by below solution. — Vishal Bareja, Aug 22 '18 at 12:31

score 1 · Accepted Answer · answered Aug 22 '18 at 12:14

I assume that you are trying to create a database by fetching database name from a query. You can use a variable as :

declare a variable and store the output of your query
use variable in dynamic sql to create a database.

example:

SELECT @id := (REPLACE( REPLACE( REPLACE( standard_name, ' ', '' ), '-', '' ), '_', '' )) FROM standard_master WHERE _id = 1;
# database name is in the @id variable
SET @SQL = CONCAT('CREATE DATABASE ', @id);
PREPARE stmt FROM @SQL;
# execute the statement that you have created
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

credit: I have used https://dba.stackexchange.com/questions/14397/creating-a-table-from-variable-names as reference for dynamic sql.

Hope it answers your query.

how to create database with subquery's return value name?

1 Answers1