MYSQL insert from select and variables

Question

I am trying to insert values coming from a select and variable :

INSERT INTO routeur (`codeAdherent`, `quantiteArticle`, `dateFin`) VALUES 
(SELECT `codeAdherent` FROM adherents WHERE categorie = 'G', `quantiteArticle` = $a, `dateFin`= $b);

Write it with and without VALUES, with and without IN, with and without brackets but I always get an synthax error.

Where is my mistake?

What values you want to insert in which fields? Your insert query is definitely wrong. — Jignesh M. Khatri
– Jignesh M. Khatri, Commented Oct 1, 2018 at 10:44

Fahmi · Accepted Answer · 2018-10-01 11:19:53Z

1

Try below:

INSERT INTO routeur (codeAdherent, quantiteArticle, dateFin) 
SELECT codeAdherent, @a, @b FROM adherents WHERE categorie = 'G'

edited Oct 1, 2018 at 11:19

answered Oct 1, 2018 at 10:42

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

6 Comments

Chamarejc Over a year ago

no change, to the try I write quantiteArticle = 1, dateFin = "2018-12-31"

Fahmi Over a year ago

what error it shows, I've edited the answer you can check now - syntax error was is where cluase

Chamarejc Over a year ago

quantiteArticle and dateFin doesn't come from adherents. There are external values.

Fahmi Over a year ago

you can check now

Chamarejc Over a year ago

Fantastic, I wrote : INSERT INTO routeur (codeAdherent, quantiteArticle, dateFin) SELECT codeAdherent, 1, "2018-12-31" FROM adherents WHERE categorie = 'G' and that's work. I don't really understand the synthax but the result is here. A question : why do you change $ to @ ?

|

kiks73 · Accepted Answer · 2018-10-01 12:18:51Z

0

You have to read carefully the INSERT syntax because you have got many errors. This is the right syntax:

INSERT INTO routeur (codeAdherent, quantiteArticle, dateFin) 
SELECT codeAdherent, '$a', '$b'
FROM adherents 
WHERE categorie = 'G'

PS: To avoid the SQL Injection you should use Prepared Statements

edited Oct 1, 2018 at 12:18

answered Oct 1, 2018 at 10:41

kiks73

3,8383 gold badges30 silver badges55 bronze badges

4 Comments

Chamarejc Over a year ago

I want to insert not only codeAdherent but also quantiteArticle and dateFin

kiks73 Over a year ago

@Chamarejc check now

Chamarejc Over a year ago

quantiteArticle and dateFin doesn't come from adherents. There are external values. Thanks for your suggestion about prepare statements

kiks73 Over a year ago

@Chamarejc in your original SQL it seems that quantity and datafin were a condition with category. Anyway I adjusted my answer.

munsifali · Accepted Answer · 2018-10-01 13:31:11Z

0

You can try this out :

INSERT INTO routeur (codeAdherent, quantiteArticle, dateFin) VALUES 
(SELECT codeAdherent FROM adherents WHERE categorie = 'G', $a, $b);

edited Oct 1, 2018 at 13:31

munsifali

1,7432 gold badges24 silver badges44 bronze badges

answered Oct 1, 2018 at 10:41

Jignesh M. Khatri

1,6561 gold badge16 silver badges29 bronze badges

1 Comment

Chamarejc Over a year ago

here under the answer : INSERT INTO routeur (codeAdherent, quantiteArticle, dateFin) VALUES (SELECT * FROM adherents WHERE categorie = 'G', quantiteArticle = 1, dateFin= "2018-12-31") MySQL a répondu: Documentation #1064 - Erreur de syntaxe près de 'SELECT * FROM adherents WHERE categorie = 'G', quantiteArticle = 1, dateFin=' à la ligne 1

Collectives™ on Stack Overflow

MYSQL insert from select and variables

3 Answers 3

6 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related