0

This is my query :

SELECT vehicle,
CASE 
WHEN vehicle IS NOT NULL
THEN (INSERT INTO tbl_vehicle_on_user (vehicle, userid) values
(SELECT `vehicle` FROM `tbl_missions` WHERE `id` = 4 ), (SELECT `id` FROM `tbl_users`     WHERE `id` = 12))
FROM tbl_missions WHERE id = 4;

I need to insert a row to tbl_vehicle_on_user when vehicle is not null on id 4. When i execute this query i receive this error from mysql workbench,

01:24:49 SELECT vehicle, CASE WHEN vehicle IS NOT NULL THEN (INSERT INTO tbl_vehicle_on_user (vehicle, userid) values (SELECT vehicle FROM tbl_missions WHERE id = 4 ), (SELECT id FROM tbl_users WHERE id = 12)) FROM tbl_missions WHERE id = 4 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO tbl_vehicle_on_user (vehicle, userid) values (SELECT vehicle FROM `tbl_mi' at line 4 0.000 sec

And i get a red line under 'INTO' when i hover over it, it says 'Syntax error, unexpected INTO, expecting ('.

I don't know what it means i tried to search the web but couldn't find anything if you know how to fix this i will appreciate it if you answer my question :)

THANKS!!

Anwar
  • 165
  • 1
  • 4
  • 11
  • Select statements are projections of columns that are to be viewed and not compatible with CRUD statements. Here's a SO answer that correctly addresses your issue: http://stackoverflow.com/questions/12587929/using-case-statement-to-either-insert-into-a-table-or-update-an-existing-row – ron tornambe Jun 25 '14 at 23:41
  • Thanks for answering, but that seems too hard for me can you do it for me .. I am not very experienced with SQL yet.. – Anwar Jun 25 '14 at 23:44

1 Answers1

1

If you already know the userid should be 12, then just use 12 instead of SELECT id FROM tbl_users WHERE id = 12. Here is a valid insert-select statement.

INSERT INTO tbl_vehicle_on_user (vehicle, userid)
SELECT `vehicle`, 12 userid 
FROM `tbl_missions` 
WHERE `id` = 4 and vehicle is not null;
Fabricator
  • 12,722
  • 2
  • 27
  • 40
  • I don't know if its gonna be 12 it was just a example and i also will not know if `id` is gonna be 4.. sorry i didn't mention that earlier. – Anwar Jun 26 '14 at 07:33
  • what is the relationship between tbl_missions and tbl_users? also if not `4`, do you mean all ids? – Fabricator Jun 26 '14 at 07:44
  • Well uhm tbl_missions does sometimes has a vehicle column that isnt NULL, if that is true than i need the id from tbl_users and the id from tbl_missions and put them together in one table (tbl_vehicle_on_user). The 4 and 12 are generated by the user in php so 4 is the id of the user that is logged in, and 12 is the missionid that he selected. – Anwar Jun 26 '14 at 07:48
  • @Anwar, can you post all 3 table's schema and how they are linked? – Fabricator Jun 26 '14 at 07:54
  • Sorry i don't have acces to them right now, can you suggest something that is should do? – Anwar Jun 26 '14 at 09:26