MYSQL Query Add Hosts to Group

[UWPVIOLATOR]

So was working with my SQL admin and got this narrowed down to the following. Yet I am still getting a error on my insert statement. What are we missing? 212 is the group ID. Those values I am querying are the asset tags of our machines but would also like to query the whole host names if possible but from what I heard I cant do a in and like statement. My select statement works perfectly.

Insert Into groupMembers (gmHostID, gmGroupID) 
SELECT hostID,212 FROM `hosts` 

WHERE Right(hosts.hostName,6) in ('D02595','110563','110687','110570','110592','111045','111050','111064','111071','96643','D02320','111921','111914','111916','110076','-98264','103070','111846','111842','111840','111459','111470','111471','111463','111437','103636','103638','103639','-97871')

[UWPVIOLATOR]

@Tom-Elliott Thank you for figure this out. Here is the correct query.

Insert into groupMembers (gmHostID,gmGroupID) Select hostID,'212' from hosts where Right(hostName,6) in ('D02595','110563')

[Tom Elliott]

@UWPVIOLATOR wrap the select statement in ( )

[UWPVIOLATOR]

Error Code: 1064. 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 ’ VALUES (‘hostID’,‘212’) SELECT hostID,212 FROM hosts WHERE Right(hosts.hos’ at line 1

[UWPVIOLATOR]

Ok so it has to be the gmGroupID. It doesnt like me just plain texting it. I am able to run this

INSERT INTO groupMembers
(gmHostID, gmGroupID)
VALUES
(8928, 212)

[Daniel Miller]

There are two distinct statement syntaxes you are dealing with here. INSERT INTO ... VALUES... is for inserting a single row into a table. INSERT INTO .... SELECT... is for copying data from a table into another table. See INSERT - MariaDB Knowledge Base. The error you posted has both VALUES and SELECT keywords indicated. Did the query that produced that error have both keywords?

[UWPVIOLATOR]

@Daniel-Miller No, I typed in 212. I could get it from another table with another where statement. I think that is the issue.

[Tom Elliott]

@UWPVIOLATOR

Insert Into groupMembers (gmHostID, gmGroupID) SELECT hostID,'212' FROM `hosts` WHERE Right(hostName,6) in ('D02595','110563','110687','110570','110592','111045','111050','111064','111071','96643','D02320','111921','111914','111916','110076','-98264','103070','111846','111842','111840','111459','111470','111471','111463','111437','103636','103638','103639','-97871')

I wonder if the space between the select and the where for the insert is playing into it. Maybe the code above on one line will work better?

[UWPVIOLATOR]

@Tom-Elliott Thank you for figure this out. Here is the correct query.

Insert into groupMembers (gmHostID,gmGroupID) Select hostID,'212' from hosts where Right(hostName,6) in ('D02595','110563')