Solved

SQL to Join and Update Tables

Posted on 2007-12-03
8
2,707 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best way to get data into a database 12 56
sql trace 4 29
T-SQL: Number of Records is Greater Than One 7 51
Database movement 2 15
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

752 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