Can someone help with a SQL insertion?

[bopper]

Hi,

We are trying to assign all items in a specific department to a specific
category. Right now we have the category listings setup, but none assigned
to any specific items. A sql command to automate this would be very helpful.


Thanks,

CS

 

[Michael]

From within Store Operations Administrator run the following SQL statements

Step 1 - Run the following SQL statement and print the results

SELECT * FROM Department

Step 2 - Run the following SQL statement and print the results

SELECT * FROM Category

Step 3 - Run the following SQL statement and substitute 999 with the
DepartmentID you wish to update and note how many records returned

SELECT ItemlookupCode, Description, DepartmentID, CategoryID
FROM Item
WHERE DepartmentID = 999

Step 4 - Run the following SQL statement and substitute 99 with the
CategoryID you wish to update and substitute 999 with the DepartmentID you
wish to update, note the number of records updated should be the same as the
number of records returned in step 3

UPDATE Item SET CategoryID = 99 WHERE DepartmentID = 999

Step 5 - Repeat steps 3 & 4 for each Department of Items you wish to update


--
Disclaimer:

Always test any queries that modify data on a test database prior to running
on your live data to ensure the desired results.

Always back-up prior to running any SQL delete/update queries on your live
data.

 

[Sean]

Backup your database first.

Run the SQL query to get your department and category id's

Select * from Department

Select * from Category

Then use this query, using X as the Department and Y as the Category

Update item
Set categoryid = 'Y'
Where departmentid = 'X'

That should do it.

Sean