asked on

SQL to Join and Update Tables

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

Ashish Patel

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

Aneesh

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

Shanmuga Sundaram D

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

smoothcat11

ASKER

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

Ashish Patel

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

ASKER CERTIFIED SOLUTION

Shanmuga Sundaram D

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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

smoothcat11

ASKER

Thanks