How to insert into a mysql table column, known its name as string?

Question

0.00/5 (No votes)

See more:

I want to insert into a MySQL table (child table) column a certain value(int value). I know that the column name is:

'parent_data_ID'

But that column name is got from the following command:

SQL

SELECT COLUMN_NAME
FROM
  information_schema.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'parent'
  and 
  TABLE_NAME = 'child'
  AND REFERENCED_COLUMN_NAME = 'ID';

What I have tried:

I have tried the following command:

insert into `pharmacy_schema`.`employees`
(SELECT COLUMN_NAME
FROM
  information_schema.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'persons_data'
  and 
  TABLE_NAME = 'employees'
  AND REFERENCED_COLUMN_NAME = 'ID')
  values('5');

But an error occurred because the column name is a string not a column name, So what should i do to retrieve the column name and use it in insertion operations?

Posted 21-Feb-19 4:30am

Ahmed AE

Updated 21-Feb-19 5:11am

Add a Solution

Comments

Richard Deeming 22-Feb-19 11:22am

REPOST
This is the same question you posted last week:
https://www.codeproject.com/Questions/1277429/How-do-I-insert-a-value-using-select-statement-in[^]

If you want to update your question to add more information, go to the original question and click the green "Improve question" link. DO NOT post your update as a new question.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2019-02-21T05:11:00

AFAIK, you cannot directly parameterize a column name like this:

INSERT INTO table (@columnName = @value)

in SQL.

You could, however, build your SQL statement in your application code and just place the name in the SQL query string. However, you better be damn sure it's not possible for just anyone to trick your code into building an SQL statement that could destroy your database. This isn't exactly a secure way to do things by an stretch of the imagination.

You'd be better off using a CASE in the INSERT statement somehow to use the columnname parameter to select the correct column name, though I can't really say that it's even legal to do so. I don't have the time right now to look it up or test it.