I don't think that will work because Context_ID is not found in 'targets_list' hence the error. That's why I thought the Context_ID field was being assigned with the value of @ContextID. That's the only way we can get this value.
What I would like is Context_ID to be filled with the user var. @Context_ID and the Target_IDs found in both the tables. So if the input is 3, then each entry should be 3 and any target ID found with the flags associated with context 3 applied. These flags are processed earlier in this same procedure and the IDs are put into the APPLICABLE_TARGET_IDS temporary table. steve--- Steven J Orton Software Engineer Northrop Grumman Mission Systems Middletown, RI 02842 -----Original Message----- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wed 6/28/2006 4:01 PM To: Orton, Steve Cc: mysql@lists.mysql.com Subject: Re: Tough query to crack Steve, > INSERT INTO master_context_list (Context_ID, Target_ID) > SELECT Context_ID = @ContextID, targets_list.Target_ID FROM targets_list > WHERE Target_ID IN ( SELECT Target_ID FROM #APPLICABLE_TARGET_IDS ) [EMAIL PROTECTED] tells the server to return 1 when the column value of context_id is equal to the value of the user variable @contextID, and otherwise zero. Not likely what you intend. Apart from that, does the following, in standard SQL, represent what you are trying to accomplish? INSERT INTO master_context_list (Context_ID, Target_ID) SELECT l.Context_ID, l.Target_ID FROM targets_list AS l INNER JOIN applicable_target_ids AS a USING (target_id); PB ----- Orton, Steve wrote: > Hello fellow listers, > > I'm currently trying to reduce our TCO by incorporating this fine DBMS and > replacing the MS SQLServer we're using. I'm trying to re-implement the > stored procedures written for SQLServer to MySQL and have one that's tough to > figure out. > > This stored procedure basically finds ID values in one table that fit > criteria based on set flags in the table and puts them in another table along > with a context value that is assigned to the 'flag' settings. This > 'master_context_list' table holds all the IDs assigned to each context value. > So when the flags are changed or another context is created, the table is > updated with new ID values that fit the new criteria. > > The stored procedures make extensive use of MS shortcuts to create tables, > assign values, etc. This is the MS SQL statement: > > INSERT INTO master_context_list (Context_ID, Target_ID) > SELECT Context_ID = @ContextID, targets_list.Target_ID FROM targets_list > WHERE Target_ID IN ( SELECT Target_ID FROM #APPLICABLE_TARGET_IDS ) > > So this is taking all the Target IDs that match in both 'targets_list' and > the temporary table APPLICABLE_TARGET_IDS and inserting them into > 'master_context_list' along with the Context_ID with the value of @ContextID. > This sort of shorthand is nice for MS users, bad for the rest of us. > > When I try to run this in a MySQL stored procedure, I get this error: > "Unknown column 'db_context_ID' in field list" > > I know why it's failing but there's a failure in translation. Can anybody > figure out how to translate this into SQL that will work in MySQL? > > Thanks in advance for any help....steve--- > > Steven J Orton > Software Engineer > Northrop Grumman Mission Systems > Middletown, RI 02842 > > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
