tableA:
id name age price
1 kaka 24 2400
2 lala 34 1800tableB:
id name age price
7 kaka 24 1400
8 nana 45 3900把tableB里的price值和tableA里的price值相加,条件是两个表的name,age相同,同时把tableA表的price变0.
目的就是把数据导到另一个表里,结果是这样的:
tableA:
id name age price
1 kaka 24 0
2 lala 34 1800tableB:
id name age price
7 kaka 24 3800
8 nana 45 3900
id name age price
1 kaka 24 2400
2 lala 34 1800tableB:
id name age price
7 kaka 24 1400
8 nana 45 3900把tableB里的price值和tableA里的price值相加,条件是两个表的name,age相同,同时把tableA表的price变0.
目的就是把数据导到另一个表里,结果是这样的:
tableA:
id name age price
1 kaka 24 0
2 lala 34 1800tableB:
id name age price
7 kaka 24 3800
8 nana 45 3900
set b.price=b.price+a.price,a.price=0
大概是这个意思.
update ppp p1,ppp2 p2 set p2.price=p2.price+p1.price where p1.name=p2.name and p1.age=p2.age
或者这么写也是对的
update ppp p1,ppp2 p2 set p2.price=p2.price+100,p1.price=p1.price-100 where p1.name=p2.name and p1.age=p2.age第一个情况是什么原因?怎么改
update (select *,@ee:=price as newp from ppp) p1,ppp2 p2 set p2.price=p2.price+p1.newpp,p1.price=0 where p1.name=p2.name and p1.age=p2.age
我直接执行这个语句,有这个错误****************************************************************************
错误
SQL 查询: UPDATE (SELECT * , @ee := price AS newp
FROM ppp
)p1, ppp2 p2
SET p2.price = p2.price + p1.newpp,
p1.price =0 WHERE p1.name = p2.name AND p1.age = p2.age MySQL 返回: #1288 - The target table p1 of the UPDATE is not updatable
*************************************************************************************
--
-- 表的结构 `ppp`
--CREATE TABLE IF NOT EXISTS `ppp` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(60) default NULL,
`age` int(11) default NULL,
`price` varchar(60) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- 表的结构 `ppp2`
--CREATE TABLE IF NOT EXISTS `ppp2` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(60) default NULL,
`age` int(11) default NULL,
`price` varchar(60) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
但
update ppp p1,ppp2 p2 set p2.price=p2.price+p1.price,p1.price=0 where p1.name=p2.name and p1.age=p2.age
这个语句测试是可以更新的,
贴几条INSERT INTO语句,我测试一下
-- 导出表中的数据 `ppp`
--INSERT INTO `ppp` (`id`, `name`, `age`, `price`) VALUES
(1, 'kala', 24, '3000'),
(2, 'nana', 26, '4000');
--
-- 导出表中的数据 `ppp2`
--INSERT INTO `ppp2` (`id`, `name`, `age`, `price`) VALUES
(1, 'kala', 24, '10100'),
(2, 'kala', 28, '2000');
mysql> SELECT * FROM PPP;
+----+------+------+-------+
| id | name | age | price |
+----+------+------+-------+
| 1 | kala | 24 | 0 |
| 2 | nana | 26 | 4000 |
+----+------+------+-------+
2 rows in set (0.00 sec)mysql> SELECT * FROM PPP2;
+----+------+------+-------+
| id | name | age | price |
+----+------+------+-------+
| 1 | kala | 24 | 13100 |
| 2 | kala | 28 | 2000 |
+----+------+------+-------+
2 rows in set (0.00 sec)