MySQL insert into values (select)

Question

I am trying to insert value into mysql 5.1 table using the following statement

INSERT INTO `bp_measurements`
(`id_patient`, `time`, `location`, `systolic`, `diastolic`) 
VALUES (
'2',
'2015-12-26 13:19:35',
(SELECT `id` FROM `gps_locations` WHERE `lon` = 20.40930 AND `lat` = 48.94990`),
'110',
'70'
)

But I am getting syntax error MySQL server version for the right syntax to use near '`),'110','70')' at line 2 (sorry for not putting this into code but it is not working because of apostrophes used). What am I doing wrong? I have tried some other solution from stackoverflow but I cant get them working

Thanks in forward

EDIT: this is the correct version of my statement

INSERT INTO `bp_measurements`
(`id_patient`, `time`, `location`, `systolic`, `diastolic`) 
VALUES (
'2',
'2015-12-26 13:19:35',
(SELECT `id` FROM `gps_locations` WHERE `lon` = 20.40930 AND `lat` = 48.94990),
'110',
'70'
)

both these and accepted answer works so feel free to use any of them

Pரதீப் · Accepted Answer · 2015-12-27 07:44:31Z

3

Try this syntax

Add the constant values to the select column list in order of insert column list

INSERT INTO `bp_measurements` 
            (`id_patient`, 
             `time`, 
             `location`, 
             `systolic`, 
             `diastolic`) 
SELECT '2', 
       '2015-12-26 13:19:35', 
       `id`, 
       '110', 
       '70' 
FROM   `gps_locations` 
WHERE  `lon` = 20.40930 
       AND `lat` = 48.94990

answered Dec 27, 2015 at 7:44

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

2 Comments

horin Over a year ago

actualy even my solution is working I just forget to remove one apostrophe I will post correct version too

Pரதீப் Over a year ago

@horin - unless your sub-query returns more than one row.

Rahul Maurya · Accepted Answer · 2015-12-27 07:57:26Z

0

Modified Insert statement as:

INSERT INTO bp_measurements(id_patient, time, location, systolic, diastolic) (SELECT '2' AS id_patient,'2015-12-26 13:19:35' AS time,id AS location,'110' AS systolic,'70' AS diastolic FROM gps_locations WHERE lon = 20.40930 AND lat = 48.94990)

answered Dec 27, 2015 at 7:57

Rahul Maurya

1875 bronze badges

Collectives™ on Stack Overflow

MySQL insert into values (select)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related