SQL command to put Supplier in Sub Discription

TAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING AND UPDATE, INSERT OR DELETE STATEMENT!

UPDATE Item SET SubDescription1 = s.name FROM Item i INNER JOIN Supplier s on i.supplierID = s.ID

This is going to update Sub Description 1 based on the PRIMARY supplier for the item.

Glenn Adams Tiber Creek C> Hi everyone,

BACKUP YOUR DATABASE.

Then try this:

In SO Admin, run the fullowing query: SELECT ID, SupplierName FROM Supplier

Export the result and open with Excel. You'll get a worksheet with ID in Column A and SupplierName in Column B. In Column C, (cell C2) enter the following formula: ="UPDATE Item SET SubDescriptionX = '"&B2&"' WHERE SupplierID = "&A2

You'll need to change the X in SubDescriptionX to 1/2/3 depending on which field you'd prefer to populate.

Use the Edit:Fill:Down (Ctrl-D) feature to create a series of query statements for each supplier.

Copy/paste the entire set of query statements into a new query window and run the queries.

The result will only reflect the last query statement run.

I'd STRONGLY recommend that you do this when the store is closed. You shouldn't have any trouble, but it's best to be cautious with this sort of thing.

HTH, Tom

"nt8378" wrote:

I like your answer better.

Tom

"Glenn Adams [MVP - Retail Mgmt]" wrote:

I finally have time to perform this statement. But when I try to run it I get the following error:

An error occurred while executing query:

Invalid column name 'name'.

Just wondering if you might know what I am doing wrong.

Thank you. Neil

"Glenn Adams [MVP - Retail Mgmt]" wrote:

That's what I get for writing SQL Code from memory... The correct column name is not supplier.Name, it's supplier.SupplierName. I've fixed the code below...

TAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING AND UPDATE, INSERT OR DELETE STATEMENT!

UPDATE Item SET SubDescription1 = s.SupplierName FROM Item i INNER JOIN Supplier s on i.supplierID = s.ID

This is going to update Sub Description 1 based on the PRIMARY supplier for the item.

Glenn Adams Tiber Creek C> I finally have time to perform this statement. But when I try to run it I get

Glenn,

Thank you very much for the SQL script. It worked great. Also thank you for the very quick replies.

Neil

"Glenn Adams [MVP - Retail Mgmt]" wrote: