> -----Original Message----- > From: Mike Soultanian [mailto:[EMAIL PROTECTED] > Sent: Saturday, January 07, 2006 4:12 PM > To: CF-Talk > Subject: question about insert select statement > > I have a user interface and it has multiple checkboxes to select what > members of a group a user is in. I have a table to store user > information (USERS), a table to store the groups (GROUPS), and a join > table to keep track of what group a user is a member of > (GROUPMEMBERS). > I set the NAME field of all the checkboxes to ffGroups and > the VALUE > for each of those checkboxes is populated with a different GROUP_ID. > URL.USER_ID is the current user I'm editing. I have the > following query > to insert the users and groups into the join table: > > INSERT INTO GROUPMEMBERS(USER_ID, GROUP_ID) > SELECT '#Val(URL.USER_ID)#', GROUP_ID > FROM GROUPS > WHERE GROUP_ID IN (#ffGroups#) > > I don't get why it's not generating an error, though. > '#Val(URL.USER_ID)#' does not exist in the GROUPS table. I > tried making > the following test query: > > <cfquery name="miketest"> > SELECT '1', GROUP_ID > FROM GROUPS > </cfquery> > > and it threw an error, so I'm guessing because that SELECT statement > replaces the VALUES portion of the INSERT statement, it lets > it fly for > some reason. Is this true? I was just curious...
In the first query, you're creating the values in the first column dynamically, so it doesn't matter that the user_id is not in the groups table. In the test query the only problem I can see is you don't have a datasource defined. Both queries should work, even if your user_id is an integer, although you shouldn't put single quotes around it if it is. Whatever database you're using (I am assuming SQL Server), an implicit conversion from char/varchar to int for the user_id column is happening behind the scenes. Andy ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228742 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
