Solved

SQL to Join and Update Tables

Posted on 2007-12-03
8
2,713 Views
Last Modified: 2010-04-21
I'm getting an error for this SQL Statement.  I'm trying to join and update the tables.  I want to update NewProductURLinfo What's wrong?  Thanks.

UPDATE b
SET a.Weight = b.Weight
FROM Dist_List a
LEFT JOIN NewProductURLinfo b
  ON b.UPC = a.UPC
WHERE a.ID > 0 AND a.ID <= 100

Error
SQL query:

UPDATE b SET a.Weight = b.Weight FROM Dist_List a LEFT JOIN NewProductURLinfo b ON b.UPC = a.UPC WHERE a.ID >0 AND a.ID <=10

MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Dist_List a
LEFT JOIN NewProductURLinfo b
  ON b.UPC = a.UPC
WHERE a.ID ' at line 3
0
Comment
Question by:smoothcat11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20396957
The set statment was wrong you were updating b table and setting column a.Weight.
Try this.


UPDATE b
SET b.Weight = a.Weight 
FROM Dist_List a
LEFT JOIN NewProductURLinfo b
  ON b.UPC = a.UPC
WHERE a.ID > 0 AND a.ID <= 100

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20396960
Hello smoothcat11,

UPDATE b
SET b.Weight = =a.Weight   --- u are updating the table 'B'  so the target should be b.Weight
FROM Dist_List a
LEFT JOIN NewProductURLinfo b
  ON b.UPC = a.UPC
WHERE a.ID > 0 AND a.ID <= 100




Aneesh R
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20396967
UPDATE b
SET a.Weight = b.Weight
FROM Dist_List a ,NewProductURLinfo b where b.UPC = a.UPC and a.ID > 0 AND a.ID <= 100
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:smoothcat11
ID: 20397026
I tried them all, I'm still getting this:

MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Dist_List a
LEFT JOIN NewProductURLinfo b
  ON b.UPC = a.UPC
WHERE ' at line 3
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20397041
Try this now
UPDATE NewProductURLinfo SET Weight = a.Weight
FROM Dist_List a
LEFT JOIN NewProductURLinfo b
  ON b.UPC = a.UPC
WHERE a.ID > 0 AND a.ID <= 100
0
 
LVL 17

Accepted Solution

by:
Shanmuga Sundaram earned 500 total points
ID: 20397061
UPDATE NewProductURLinfo b, Dist_List a
SET a.Weight = b.Weight
WHERE b.UPC = a.UPC and a.ID > 0 AND a.ID <= 100
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20397384
>>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version<<
Since you included the MS SQL Server zone you will get T-SQL solutions that may not be compatible to MySQL.
0
 

Author Closing Comment

by:smoothcat11
ID: 31412377
Thanks
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question