[ic] SQL INSERT

[Scott Underwood]

Scott,
	My DB is postgresql and this (I beleive) would work for me. I see 
now that you are using mysql and I'm not sure that mysql supports these 
sub selects. Hope someone on the list can give you help with that 
question. The error looks like mysql doesn't like the parens. Try it 
without the parens and see if it works.

What I would do on this is first make sure the Select statement works by 
itself and then add the update statement so you can tell where it's 
failing. First make sure that mysql can handle this kind of a subselect 
statement. Sorry if I caused you the confusion, not sure how mysql likes 
the statements.

On Thu, 7 Feb 2002, Scott Andreas wrote:

> 
> 
> -----Original Message-----
> From: interchange-users-admin@interchange.redhat.com
> [mailto:interchange-users-admin@interchange.redhat.com]On Behalf Of
> Scott Andreas
> Sent: Monday, February 04, 2002 10:50 AM
> To: 'IC mailing list'
> Subject: [ic] SQL INSERT
> 
> 
> hello list,
> 
> I'm still having trouble with an INSERT command based on a select
> 
> Is what I want is in my contacts table I'm selected certain criteria and
> then inserting a note into my contactnotes table certain values
> 
> Here's what I have so far
> 
> contactnotes mysql table headers:
> ________________________________________
>      |           |      |        |
> code + contactno + note + source + date
> _____|___________|______|________|______
> 
> 
> [tmp dropdate][tag op=time]%Y%m%d[/tag][/tmp]
> [tag flag write]contactnotes[/tag]
> [query list=1
> 	st=db
> 	table=contactnotes
> 	sql="INSERT INTO contactnotes (contactno, notes, source, date) values
> 	(SELECT  catalogs.code, 'show catalog sent to user' , 'catalogs' ,
> '[scratch dropdate]'
> 	FROM catalogs
> 	WHERE country = 'US' AND show_qty = '1' AND completed != '1')
> "]
> [/query]
> 
> The values that I want to insert into contactnotes are
> code = AUTO_INCREMENT
> contactno = catalogs.code    <--- code column from catalogs table
> note = Show catalog sent to user
> source = catalogs
> date = [scratch dropdate]
> 
> All based on the select statement
> 
> The error file shows...
> 
> /intranet/admin/insert_contactnotes.html Bad SQL statement: Parse error near
> SELECT  catalogs.code, 'show catalog sent to user' , 'catalogs' , '20020207'
>  	FROM catalogs
>  	WHERE country = 'US' AND show_qty = '1' AND completed != '1')
> 
>  Query was: INSERT INTO contactnotes (contactno, notes, source, date) values
>  	(SELECT  catalogs.code, 'show catalog sent to user' , 'catalogs' ,
> '20020207'
> 	FROM catalogs
> 	WHERE country = 'US' AND show_qty = '1' AND completed != '1')
> 
> 
> 
> Any help would be appreciated
> 
> 
> J. Scott Andreas :)
> IS/Programmer/WebDevelopement
> Learning Services, Inc.
> e-mail: sandreas@learningservicesinc.com
> phone: 1-800-877-9378 ext. 146
> fax: (541) 744-2056
> 
> 
>        ---    __o
>      ---   _-\<,_
>       --- (_)/ (_)
> 
> 
> _______________________________________________
> interchange-users mailing list
> interchange-users@interchange.redhat.com
> http://interchange.redhat.com/mailman/listinfo/interchange-users
> 
> _______________________________________________
> interchange-users mailing list
> interchange-users@interchange.redhat.com
> http://interchange.redhat.com/mailman/listinfo/interchange-users
> 

-- 
Scott Underwood

"-- challenging authority and insisting that it justify itself -- 
    are appropriate at all levels."

	--Noam Chomsky