Query problem with insert into....select
hi, i have 2 tables: currencyrates +-+++ | code char(3)|| currency char(3) | | name varchar(10)|| rate double| | bcurrency char(1) || ryear varchar(4) | | || rmonth varchar(10) | +-+++ I'm have a form, from which i pick 2 value 2000 and february. I'm trying to insert fields form currency to rates, matching particular conditions in order to avoid data duplication. The rate table basically has the exchange rate for a particular currency in a particular year and a particular month. The trio currency,ryear,rmonth SHOULD be unique. My query is the following: insert into rates (currency,ryear,rmonth) select a.code, 2000, february from currency a, rates b where a.bcurrency=N and a.codeb.currency and b.ryear=2000 and b.rmonth=february _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with INSERT INTO ... SELECT
Hello, This is the situation: Table1: ID Startnr TijdAfstand SlagDatum Opmerking CRvan CRtot PR Categorie Table2: Identical columns. What is the problem? I'm trying to append all the values from table2 to table1. I was trying this SQL query: insert into table1 select Startnr, Tijd, Afstand, Slag, Datum, Opmerking, CRvan,CRtot,PR,Categorie from table2; This doesn't work, it says Column count doesn't match value count at row 1 I understand the problem. But I can't use my ID column in the select query because both tables have an AUTO INCREMENT ID. Does someone have any suggestions how to work around this?? Thanks in advance Michiel Leegwater - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with INSERT INTO ... SELECT
Michiel i believe you need to tell the target what fields it will have too... INSERT INTO Table1 (A, B) SELECT Table2.A, Table2.B FROM Table2 Of course you can omit the Table2. part in this trivial case. making it INSERT INTO Table1 (A, B) SELECT A, B FROM Table2 -Original Message- From: Michiel Leegwater [mailto:[EMAIL PROTECTED]] Sent: Monday, August 20, 2001 3:25 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Problem with INSERT INTO ... SELECT Hello, This is the situation: Table1: ID Startnr TijdAfstand SlagDatum Opmerking CRvan CRtot PR Categorie Table2: Identical columns. What is the problem? I'm trying to append all the values from table2 to table1. I was trying this SQL query: insert into table1 select Startnr, Tijd, Afstand, Slag, Datum, Opmerking, CRvan,CRtot,PR,Categorie from table2; This doesn't work, it says Column count doesn't match value count at row 1 I understand the problem. But I can't use my ID column in the select query because both tables have an AUTO INCREMENT ID. Does someone have any suggestions how to work around this?? Thanks in advance Michiel Leegwater - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with INSERT INTO ... SELECT
On Mon, 20 Aug 2001, Michiel Leegwater wrote: insert into table1 select Startnr, Tijd, Afstand, Slag, Datum, Opmerking, CRvan,CRtot,PR,Categorie from table2; This doesn't work, it says Column count doesn't match value count at row 1 I understand the problem. But I can't use my ID column in the select query because both tables have an AUTO INCREMENT ID. Does someone have any suggestions how to work around this?? What if you do: SELECT NULL, Startnr, Tijd, ... FROM table2 Put NULL in place of the AUTO INCREMENT id. That might work. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php