MYSQL Query Add Hosts to Group

  • 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')

  • @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')

  • Senior Developer


    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?

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

  • 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?

  • 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)
    (8928, 212)

  • 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

  • Senior Developer

    @UWPVIOLATOR wrap the select statement in ( )

Log in to reply