[ic] SQL INSERT

[Scott Underwood]

Scott,
	The SQL you're using below is not quite what you need.
[snip]
> INSERT INTO contactnotes   
>       SELECT code, country, show_qty, completed
>       FROM contacts
>       WHERE country = US AND show_qty = 1 AND completed != 1
[/snip]

When you use this syntax you must insert a value for every column in your 
table. It appears that your contactnotes table has 5 columns and you're 
only selecting 4 values. You would think the auto-inc field would not need 
to be specified but it does. Try the below syntax instead.

   INSERT INTO contactnotes (contactno, note, source, date) values   
       (SELECT code, country, show_qty, completed
       FROM contacts
       WHERE country = US AND show_qty = 1 AND completed != 1)

The values you select for in your select statement will need to match the 
values that you are inserting, so I'm not sure you're selecting the 
correct information. code seems like the right thing but country = note? 
or source=show_qty or date=completed? However you can use strings in your 
select statement. 

   INSERT INTO contactnotes (contactno, note, source, date) values
       (SELECT code, 'Show catalog sent to user', 'catalogs', 'now'
       FROM contacts            
       WHERE country = US AND show_qty = 1 AND completed != 1)

That now in the select statement above is just a way for postgresql to get 
todays date. Some syntax may be a little off but this is the way you need 
to do it and the auto-inc field will be taken care of by the DBMS.

Hope this helps some.



On Mon, 4 Feb 2002, Scott Andreas wrote:

> hello list,
> 
> I'm 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
> _____|___________|______|________|______
> 
> 
> INSERT INTO contactnotes
> 	SELECT code, country, show_qty, completed
> 	FROM contacts
> 	WHERE country = US AND show_qty = 1 AND completed != 1
> 
> The values that I want to insert into contactnotes are
> code = AUTO_INCREMENT
> contactno = code.contacts
> note = Show catalog sent to user
> source = catalogs
> date =$tag->time{%Y%M%d}
> 
> All based on the select statement
> 
> 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
> 

-- 
Scott Underwood

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

	--Noam Chomsky