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
