Hi everyone,
I am wondering if anyone knows of an SQL command that will copy an items supplier to a sub-discription field. I will need to do this for all of my items in my database. If anyone knows any quick tricks I will be happy to listen.
Thanks, Neil
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:
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.