表名是fsdout ,表里面主要这几项,指定departure ,arrival,filingAirline,还有fareBasis,singlefare,isvalid ,当一个fareBasis对应两个singlefare,我们取大singlefare,同时将isvalid 置为1,语句如下
set @b='BJS';
set @c='LAX';
set @d='AC';
UPDATE fsdout f
set isvalid = '1'
where updateTime>'2016-04-12' and f.rowid in (SELECT a.rowid
FROM fsdout a
WHERE `departure` = @b AND `arrival` = @c AND `filingAirline` = @d and singlefare in (select max(singlefare) from fsdout where fareBasis=a.fareBasis and `departure` = @b AND `arrival` = @c AND `filingAirline` = @d ));
更新处一直显示错误,Error in query (1054): Unknown column 'a.rowid' in 'field list'
更新操作改为
set @b='BJS';
set @c='LAX';
set @d='AC';
UPDATE fsdout
set isvalid = '1'
where updateTime>'2016-04-13' and isvalid in (SELECT isvalid
FROM fsdout a
WHERE `departure` = @b AND `arrival` = @c AND `filingAirline` = @d and singlefare in (select max(singlefare) from fsdout where fareBasis=a.fareBasis and `departure` = @b AND `arrival` = @c AND `filingAirline` = @d ) );
显示错误Error in query (1093): You can't specify target table 'fsdout' for update in FROM clause里面选择程序运行正确,就是更新有问题,求助大神~~~~~
set @b='BJS';
set @c='LAX';
set @d='AC';
UPDATE fsdout f
set isvalid = '1'
where updateTime>'2016-04-12' and f.rowid in (SELECT a.rowid
FROM fsdout a
WHERE `departure` = @b AND `arrival` = @c AND `filingAirline` = @d and singlefare in (select max(singlefare) from fsdout where fareBasis=a.fareBasis and `departure` = @b AND `arrival` = @c AND `filingAirline` = @d ));
更新处一直显示错误,Error in query (1054): Unknown column 'a.rowid' in 'field list'
更新操作改为
set @b='BJS';
set @c='LAX';
set @d='AC';
UPDATE fsdout
set isvalid = '1'
where updateTime>'2016-04-13' and isvalid in (SELECT isvalid
FROM fsdout a
WHERE `departure` = @b AND `arrival` = @c AND `filingAirline` = @d and singlefare in (select max(singlefare) from fsdout where fareBasis=a.fareBasis and `departure` = @b AND `arrival` = @c AND `filingAirline` = @d ) );
显示错误Error in query (1093): You can't specify target table 'fsdout' for update in FROM clause里面选择程序运行正确,就是更新有问题,求助大神~~~~~
set isvalid = '1'
from fsdout
where updateTime>'2016-04-13' and isvalid in (SELECT isvalid
FROM fsdout a
WHERE `departure` = @b AND `arrival` = @c AND `filingAirline` = @d and singlefare in (select max(singlefare) from fsdout where fareBasis=a.fareBasis and `departure` = @b AND `arrival` = @c AND `filingAirline` = @d )
from fsdout
where updateTime>'2016-04-13' and isvalid in (SELECT isvalid
FROM fsdout a
WHERE `departure` = @b AND `arrival` = @c AND `filingAirline` = @d and singlefare in (select max(singlefare) from fsdout where fareBasis=a.fareBasis and `departure` = @b AND `arrival` = @c AND `filingAirline` = @d )) set isvalid = '1'试试看这样行不行 MYSQL的语法有点忘记了
才能帮你写SQL喔.
set isvalid = '1'
where updateTime>'2016-04-12' and id in (502536418,502536419,502536420);能运行,结果正确
但是
UPDATE fsdout
set isvalid = '1'
where updateTime>'2016-04-12' and id in (select id from fsdout);不能运行,提示错误Error in query (1093): You can't specify target table 'fsdout' for update in FROM clause
比如
fareBasis singlefare isvalid
A 5 1
A 3 0
B 10 1
B 8 0
set @c='LAX';
set @d='AC';
UPDATE fsdout a
set isvalid = '1'
where a.updateTime>'2016-04-13' and a.isvalid in (SELECT isvalid
FROM fsdout
WHERE `departure` = @b AND `arrival` = @c AND `filingAirline` = @d and singlefare in (select max(singlefare) from fsdout where fareBasis=a.fareBasis and `departure` = @b AND `arrival` = @c AND `filingAirline` = @d ) );