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
– Joakim Danielson, Commented Aug 22, 2018 at 11:59
Question edited. now you understand my question @JoakimDanielson — Vishal Bareja
– Vishal Bareja, Commented Aug 22, 2018 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 for this. — Solarflare
– Solarflare, Commented Aug 22, 2018 at 12:09
You need to build a string containing your sql code and execute it, see stackoverflow.com/questions/999200/… — Joakim Danielson
– Joakim Danielson, Commented Aug 22, 2018 at 12:18

Sahith Vibudhi · Accepted Answer · 2018-08-22 12:14:41Z

1

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.

answered Aug 22, 2018 at 12:14

Sahith Vibudhi

5,2452 gold badges36 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related