Solved

SQL to Join and Update Tables

Posted on 2007-12-03
8
2,692 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
 

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Amazon Redshift 2 24
Azure SQL Insert not working suddenly 11 18
SQL Server merge records in one table 2 10
SQL Pivot add row totals 2 0
Creating and Managing Databases with phpMyAdmin in cPanel.
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…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now