Updating a table based row content
There are many times in SQL, that I have needed to update the information in a table, based on specific information for a particular row. Often, this is when I have built a scratch table to analyze information, and populated it with data from other tables. (See my previous blog for how that is done.)
Once the table is built, then it needs to be updated with additional information. I find this useful when building tables to analyze information from several different tables. It can be that using join clauses would be very complicated, or when the base table has been built, and a column, or columns needs to be updated later.
Say that I wanted a table of item numbers, and then periodically update it with a count of how many of those items are on order. While this could be done using a "select join" statement, there are times that having a separate table can be useful.
To do this, a table is created with the item number, and a count. The table is populated with the item number, letting the count column be null. Later it is updated with the count from the order table.
From my last blog, you know that the first part is done as follows (assuming ITEMS is a table of item numbers):
CREATE TABLE ITEMS_ON_ORDER (ITEM_NO varchar(20), ON_ORDER int)
INSERT INTO ITEMS_ON_ORDER (ITEM_NO) SELECT ITEM_NO from ITEMS
This will populate the ITEM_NO column with all the item numbers, and the ON_ORDER column will be null.
So, to update this table, the number of items on order will be counted. The trick, is to count those on order, that match the item number in the ITEM_NO column. To do this, the ITEM_NO column from the ITEMS_ON_ORDER is referenced, when updating the ON_ORDER column by doing a "SELECT COUNT(*)".
The update statement would be (assuming the orders lines are in a table named ORDER_LINES):
UPDATE ITEMS_ON_ORDER SET ON_ORDER=(SELECT COUNT(*) FROM ORDER_LINES WHERE ITEM_NO=ITEMS_ON_ORDER.ITEM_NO)
What this does, is update each row in the ITEMS_ON_ORDER table. The ON_ORDER column is set to the count or records in the ORDER_LINES table, that have the same ITEM_NO as the value of the ITEM_NO field in the ITEMS_ON_ORDER table. The reference back to the ITEMS_ON_ORDER table in the WHERE clause, is the trick to getting the enclosed SELECT statement to only count the records in ON_ORDER for each specific item in ITEMS_ON_ORDER.
Contact CCS Retail Systems, Inc. for your SQL Support and Customization needs.