CODEWITHSUNDEEP
mysqlsql
Pavlos Papanikolaou
Pavlos Papanikolaou

Reputation: 603

Updating a table using query result

In a MySQL database I have the following tables

CREATE TABLE `store` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `storetypeID` int(10) unsigned NOT NULL,
  `storename` varchar(100) NOT NULL,
  `VATnumber` char(9) NOT NULL,
  `visits` int(10) unsigned DEFAULT '0000000000',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  UNIQUE KEY `VATnumber_UNIQUE` (`VATnumber`),
  KEY `storeID_idx` (`storetypeID`),
  CONSTRAINT `storeID` FOREIGN KEY (`storetypeID`) REFERENCES `storetype` (`ID`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=947 DEFAULT CHARSET=utf8;

CREATE TABLE `expenses` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `storeID` int(10) unsigned DEFAULT NULL,
  `expensedate` date NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `storeID_idx` (`storeID`),
  CONSTRAINT `storeIDidx` FOREIGN KEY (`storeID`) REFERENCES `store` (`ID`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7502 DEFAULT CHARSET=utf8;

Using the following query I get the number of expenses for each store.

SELECT COUNT(*) AS visits, storeID FROM taxfriend.expenses GROUP BY storeID ORDER BY visits desc

Having this result I want to update the field visits in table store with the field visits form the query. One option is to write a small subroutine (e.g. using VB).

Is there any way to create an UPDATE query in MySQL to accomplish this?

Upvotes: 0

Views: 31

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Use a join in the update:

update store s join
       (SELECT COUNT(*) AS visits, storeID
        FROM taxfriend.expenses
        GROUP BY storeID
       ) sv
       on sv.storeId = s.id
    set s.visit = sv.visits;

This only updates the value once, though, to the current number. To keep the value up-to-date, you would need to use a trigger.

That is why it is often better to calculate such values on the fly. Of course, if your visits tables is really big, then the trigger may be the best approach to keeping the value up-to-date.

Upvotes: 0

Barmar
Barmar

Reputation: 781380

You can use JOIN in an UPDATE query.

UPDATE store AS s
JOIN (SELECT COUNT(*) AS visits, storeID 
      FROM taxfriend.expenses 
      GROUP BY storeID) AS e
ON s.ID = e.storeID
SET s.visits = e.visits

Upvotes: 1

Related Questions