mysql 怎样才能对同一张表查询后再更新?
请高手指点,我对一张表查询后再更新,查询是子查询,报错:
You can't specify target table 'a_table' for update in from clause
完整SQL语句是
UPDATE a_table SET STATUS=0 WHERE ID=(SELECT MIN(ID) from a_table WHERE STATUS=1 AND SID=12321);
这是MYSQL决定了对此写法不兼容吗?有什么方法可以不用拆分人两条?
mysql不允许更新或者删除一个表中数据的同时从同一个表中查询，用连接查询。
SQL code:
UPDATE a_table a INNER JOIN
(SELECT MIN(id) mid
from a_table WHERE status=1 AND sid=12321) b
ON a.id=b.mid
SET a.status=0
UPDATE a_table A ,(SELECT MIN(ID) AS MI from a_table WHERE STATUS=1 AND SID=12321) B
SET A.STATUS=0
WHERE A.ID=B.MI;
相关问答：
我是用mysql自带的C API
if(mysql_real_connect(&mysql,"125.0.0.108","root","root","home",3306,NULL,0))
{
AfxMessageBox("数据库连接失败") ......
我以前安装了一次，后来卸载了，现在再安装的时候，提示错误：Error 1305.Error reading from file C:DOCUME~1\LOCALS~1\Temp\mysql_server.msi.Verify that the file exists and that you can access it.
可是我找 ......
请问下能不能再windows下定期清空mysql一个表中的全部数据，用PHP实现！
谢谢各位了
就比如一个星期清空一次
比如每个星期六零点清空一次！
谢谢了
计划任务...php....sql...truncate....OK
你就这接在页� ......
SQL code:
create PROCEDURE aa(SqlCMD1 varchar(8000),SqlCMD2 varchar(8000),SqlCMD3 varchar(8000))
begin
declare exit handler for sqlexception rollback;
start TRANSACTION;
EXECUTE SqlCMD1;
EXE ......
SQL code:
CREATE PROCEDURE A()
BEGIN
DECLARE 'Constraint Violation'
CONDITION FOR sqlexception;
DECLARE EXIT HANDLER FOR
'Constraint Violation' ROLLBACK;
BEGIN TRAN ......
