You need to include the field names along with the table name. UPDATE table1 INNER JOIN Table1 ON Table2.FieldID = Table1.FieldID Set Table1.Field1=Table2.Field1, Table1.Field2=Table2.Field2, ...
Bruce Lewis ----- Original Message ----- From: "Greg Knaddison" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 16, 2002 5:15 PM Subject: RE: Problem with UPDATE table ... can u help plz? When I try that modified for my tables that fit this hypothetical, I get: CREATE TABLE `sales_table` ( `item_s` varchar(100) default '', `price_s` varchar(100) default '' ) TYPE=MyISAM CREATE TABLE `lookup_table` ( `item_l` varchar(100) default '', `price_l` varchar(100) default '' ) TYPE=MyISAM mysql> UPDATE sales_table SET sales_table.price_s = lookup_table.price_l INNER JOIN sales_table ON lookup_table.item_l = sales_table.item_s; [localhost] ERROR 1064: You have an error in your SQL syntax near 'INNER JOIN sales_table ON lookup_table.item_l = sales_table.item_s' at line 1 The "Update Syntax" Manual page 6.4.5 http://www.mysql.com/doc/en/UPDATE.html doesn't mention INNER JOIN and the comments on that page seem to indicate that this is a functionality lacking in MySQL though the last comment on the page shows a kluge workaround. Greg -----Original Message----- From: Bruce Lewis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 16, 2002 4:03 PM To: STIBS; [EMAIL PROTECTED] Subject: Re: Problem with UPDATE table ... can u help plz? You need an InnerJoin to perform the operation similar to: UPDATE table1 SET table1.field1 = table2.field1, table1.field2 = table2.field2,... INNER JOIN Table1 ON Table2.ID = Table1.ID Bruce Lewis ----- Original Message ----- From: "STIBS" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 16, 2002 4:26 PM Subject: Problem with UPDATE table ... can u help plz? Hi from Dresden, Germany! I have a littlebig problem: I want to UPDATE table1.fields with data from table2.fields WHERE the id (in this case a product number) is the same in both tables. OK, I did this: UPDATE table1 SET table1.field1 = table2.field1, table1.field2 = table2.field2,... WHERE table1.id = table2.id; The answer from MySQL is: Unknown table 'table2' in WHERE clause . Heading to MySQL.com the docs tell me nothing than there is a INSERT ... SELECT. In my case this would be an UPDATE ... SELECT coz I don't want a new table, just updating records of an existing with data from another. I'm sure I just miss the right topic to look at. Point me please. Second try: I used UPDATE table1, table2 SET table1.field1 = table2.field1, table1.field2 = table2.field2,... WHERE table1.id = table2.id; It still gives me an error, now in the SQL Syntax when I set the table1.field2 value. It doesn't find the second tables name [You have an error... near ... table(2.field2 should be here)]. Could someone give me the right direction? TIA! Michael Stibane (STIBS) [EMAIL PROTECTED] Training, Linux, Admin, Programming, Web http://www.stibs.cc Escapade Server-Side Scripting Engine Development Team Pensacola - Dallas - Dresden - London http://www.escapade.org --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
