?
Solved

SQL to Join and Update Tables

Posted on 2007-12-03
8
Medium Priority
?
2,718 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
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

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 2000 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

598 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