[ic] SQL INSERT

[interchange-users@interchange.redhat.com (Scott Andreas)]

Thank you very much 8-)

One other question although I didn't use column "code" for the insert
contactnotes mysql table headers:
________________________________________
     |           |       |        |
code + contactno + notes + source + date
_____|___________|_______|________|______

I thought that  INSERT INTO contactnotes (contactno, notes, source, date)
would auto_increment the column but It didn't.

So if I use (code, contactno, notes, source, date)
And use the Select statement of SELECT X catalogs.code, 'show catalog sent
to user' , 'catalogs' , '[scratch dropdate]'
>	FROM catalogs
>	WHERE country = 'US' AND show_qty = '1' AND completed != '1'
 what would I use to set the default auto_increment values for X. My
contactnotes.sql is...

Database	contactnotes	contactnotes.txt	__SQLDSN__
#ifdef SQLUSER
Database	contactnotes	USER	__SQLUSER__
#endif
#ifdef SQLPASS
Database	contactnotes	PASS	__SQLPASS__
#endif
Database	contactnotes	AUTO_NUMBER	CN000105
Database	contactnotes	COLUMN_DEF	"code=varchar(10) NOT NULL PRIMARY KEY"
Database	contactnotes	COLUMN_DEF	"contactno=varchar(10)"
Database	contactnotes	COLUMN_DEF	"notes=varchar(255)"
Database	contactnotes	COLUMN_DEF	"source=varchar(20)"
Database	contactnotes	COLUMN_DEF	"date=varchar(10)"


Database	contactnotes	DELIMITER	TAB
Database	contactnotes	EXCEL	1
Database	contactnotes	WRITE_ALWAYS	1

Tnaks in adv.

Scott


-----Original Message-----
From: interchange-users-admin@interchange.redhat.com
[mailto:interchange-users-admin@interchange.redhat.com]On Behalf Of
Kevin Walsh
Sent: Thursday, February 07, 2002 2:47 PM
To: interchange-users@interchange.redhat.com
Subject: RE: [ic] SQL INSERT


> [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]
>
Get rid of the "list=1" for a start.  (Nothing to do with your problem;
it is just not useful when doing an INSERT).

>
> 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')
>
The error file correctly shows that this is not an Interchange problem.
Instead, it is a problem with your MySQL syntax.

Have you tried running your query using an SQL command line tool?

Try this:

    INSERT INTO table1 (col1, col2, col3)
    SELECT col1, col2, col3 from table2 WHERE foo = 'bar'

Basically, remove the extra "(" and ")" you have surrounding your
SELECT.

While you are editing, you may also find it helpful to correct the
date format from your yyyymmdd to yyyy-mm-dd.

--
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin@cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/

_______________________________________________
interchange-users mailing list
interchange-users@interchange.redhat.com
http://interchange.redhat.com/mailman/listinfo/interchange-users