[Err] 1241 - Operand should contain 1 column(s)

Hi there, I need your appreciated help.

I try this sql syntax because I need update the mysql table with output of a select query.

I need update the field payoff in the tbl_payoff with on value’s produced in the select query, but I have error in the query.

The select query tested individually working and produced the correct output.

How to update the field payoff in this case ?

Can you help me?
Thanks in advance.


[SQL] UPDATE tbl_payoff
SET payoff =

(

SELECT a1.area, (sum1/sum2)*15000 `totalSum`
FROM ( 
      SELECT area,
             COUNT(*) `sum1`
      FROM tbl_sales
      WHERE (YEAR(date_of_sale)='2012')
      GROUP BY area
   )a1
JOIN 
   ( 
        SELECT area,
               SUM( Ordinary_Work_hours + Extra_Work_hours ) `sum2`
        FROM tbl_sellers
        WHERE current_year = '2012'
        GROUP BY area
) a2
ON a1.area = a2.area
);
[Err] 1241 - Operand should contain 1 column(s)



/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50515
Source Host           : localhost:3306
Source Database       : _mysql2

Target Server Type    : MYSQL
Target Server Version : 50515
File Encoding         : 65001

Date: 2012-02-29 19:27:30
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `tbl_payoff`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_payoff`;
CREATE TABLE `tbl_payoff` (
  `payoff` decimal(10,2) DEFAULT NULL,
  `area` varchar(10) DEFAULT NULL,
  `ID_payoff` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID_payoff`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_payoff
-- ----------------------------
INSERT INTO `tbl_payoff` VALUES (null, 'A', '1');
INSERT INTO `tbl_payoff` VALUES (null, 'B', '2');
INSERT INTO `tbl_payoff` VALUES (null, 'C', '3');




/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50515
Source Host           : localhost:3306
Source Database       : _mysql2

Target Server Type    : MYSQL
Target Server Version : 50515
File Encoding         : 65001

Date: 2012-02-29 19:27:37
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `tbl_sales`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_sales`;
CREATE TABLE `tbl_sales` (
  `date_of_sale` date DEFAULT NULL,
  `area` varchar(100) DEFAULT NULL,
  `ID_tbl_sales` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID_tbl_sales`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_sales
-- ----------------------------
INSERT INTO `tbl_sales` VALUES ('2012-02-01', 'A', '1');
INSERT INTO `tbl_sales` VALUES ('2012-02-09', 'A', '2');
INSERT INTO `tbl_sales` VALUES ('2012-02-13', 'B', '3');
INSERT INTO `tbl_sales` VALUES ('2012-02-14', 'C', '4');




/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50515
Source Host           : localhost:3306
Source Database       : _mysql2

Target Server Type    : MYSQL
Target Server Version : 50515
File Encoding         : 65001

Date: 2012-02-29 19:27:42
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `tbl_sellers`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_sellers`;
CREATE TABLE `tbl_sellers` (
  `sellers` varchar(100) DEFAULT NULL,
  `current_year` int(4) DEFAULT NULL,
  `Ordinary_Work_hours` int(10) DEFAULT NULL,
  `Extra_Work_hours` int(10) DEFAULT NULL,
  `area` varchar(100) DEFAULT NULL,
  `ID_tbl_Sellers` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID_tbl_Sellers`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_sellers
-- ----------------------------
INSERT INTO `tbl_sellers` VALUES ('Mark A', '2012', '45', '11', 'A', '1');
INSERT INTO `tbl_sellers` VALUES ('John B', '2012', '66', '69', 'A', '2');
INSERT INTO `tbl_sellers` VALUES ('Ronald R', '2012', '25', '14', 'C', '3');
INSERT INTO `tbl_sellers` VALUES ('Victoria S', '2012', '39', '75', 'B', '4');

you cannot just slap UPDATE in front of your query :slight_smile:

each column in the SET clause (you have only one, payoff) must be given a single value

you got the error because you were trying to set payoff to a table of results (which is what your SELECT produces)

you have to rewrite the update statement as a joined update

there are examples in the manual under the UPDATE syntax

:slight_smile:

I’m sorry.
I don’t understand your suggestion:


[SQL] UPDATE tbl_payoff t
JOIN

(

SELECT a1.area, (sum1/sum2)*15000 `totalSum`
FROM (
      SELECT area,
             COUNT(*) `sum1`
      FROM tbl_sales
      WHERE (YEAR(date_of_sale)='2012')
      GROUP BY area
   )a1
JOIN
   (
        SELECT area,
               SUM( Ordinary_Work_hours + Extra_Work_hours ) `sum2`
        FROM tbl_sellers
        WHERE current_year = '2012'
        GROUP BY area
) a2
ON a1.area = a2.area
)

SET payoff = (sum1/sum2)*15000 ;
[Err] 1248 - Every derived table must have its own alias


UPDATE tbl_payoff t
JOIN
(
--this is your derived table
) [COLOR="#0000FF"][B]AS derivedtablealias[/B][/COLOR]
SET payoff = (sum1/sum2)*15000 ;

strong hint: you are also going to want to use an ON clause for that join

I try this:

UPDATE tbl_payoff t
JOIN

(

SELECT a1.area, (sum1/sum2)*15000 `totalSum`
FROM (
      SELECT area,
             COUNT(*) `sum1`
      FROM tbl_sales
      WHERE (YEAR(date_of_sale)='2012')
      GROUP BY area
   )a1
JOIN
   (
        SELECT area,
               SUM( Ordinary_Work_hours + Extra_Work_hours ) `sum2`
        FROM tbl_sellers
        WHERE current_year = '2012'
        GROUP BY area
) a2
ON a1.area = a2.area
) AS q

SET payoff = (q.totalSum);

Output:

payoff	area	ID_payoff
157,07	A	1
157,07	B	2
157,07	C	3

All records updates with values of first totalsum… :confused:

dude, you forgot to join “q” properly

i gave you a strong hint about that

:cool:

Your strong hint was effective… Thanking You my friend !!!

UPDATE tbl_payoff t
JOIN
(

SELECT a1.area, (sum1/sum2)*15000 `totalSum`
FROM (
      SELECT area,
             COUNT(*) `sum1`
      FROM tbl_sales
      WHERE (YEAR(date_of_sale)='2012')
      GROUP BY area
   )a1
JOIN
   (
        SELECT area,
               SUM( Ordinary_Work_hours + Extra_Work_hours ) `sum2`
        FROM tbl_sellers
        WHERE current_year = '2012'
        GROUP BY area
) a2
ON a1.area = a2.area
) q

[B]ON t.area = q.area[/B]

SET payoff = (q.totalSum);


payoff	area	ID_payoff
157,07	A	1
131,58	B	2
384,62	C	3