mysql查询有什么问题？

发布于 2024-08-28 16:28:17 字数 1020 浏览 3 评论 0原文

我使用以下 mysql 查询，

DELIMITER $$
DROP PROCEDURE IF EXISTS `allied`.`aboutus_delete`$$
CREATE DEFINER=`allied`@`%` PROCEDURE `aboutus_delete`(
IN p_Id int(11)
)
BEGIN
   if exists(   select aboutUsId 
                  from aboutus 
                 where aboutUsId=p_id 
                   and isDeleted=0
            )
      update aboutus set isDeleted=1 where aboutUsId=p_id
   else
      select 'No record to delete'
END$$
DELIMITER ;

但是当我执行它时出现此错误...

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 
'update aboutus set isDeleted=1 where aboutUsId=p_id
else
   select 'No record to' at line 6

编辑：

使用分号似乎不起作用，

if exists(select aboutUsId from aboutus where aboutUsId=p_id and 
 isDeleted=0) then
   update aboutus set isDeleted=1 where aboutUsId=p_id;
else
   select 'No record to delete';

原文

I use the following mysql query,

DELIMITER $
DROP PROCEDURE IF EXISTS `allied`.`aboutus_delete`$
CREATE DEFINER=`allied`@`%` PROCEDURE `aboutus_delete`(
IN p_Id int(11)
)
BEGIN
   if exists(   select aboutUsId 
                  from aboutus 
                 where aboutUsId=p_id 
                   and isDeleted=0
            )
      update aboutus set isDeleted=1 where aboutUsId=p_id
   else
      select 'No record to delete'
END$
DELIMITER ;

But i get this error when i execute it...

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 
'update aboutus set isDeleted=1 where aboutUsId=p_id
else
   select 'No record to' at line 6

EDIT:

using semicolon doesn't seem to work,

if exists(select aboutUsId from aboutus where aboutUsId=p_id and 
 isDeleted=0) then
   update aboutus set isDeleted=1 where aboutUsId=p_id;
else
   select 'No record to delete';

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

复古式 2024-09-04 16:28:17

这是一个不同的问题：您可以稍微优化此过程。当一个查询就可以完成时，为什么要访问数据存储两次？只需将 isDeleted 属性设置为 1，然后检查 row_count 值即可。

BEGIN
  UPDATE aboutus SET isDeleted = 1 WHERE aboutUsId = p_id AND isDeleted = 0;
  IF (SELECT row_count()) <= 0 THEN
    SELECT 'No record to delete';
  END IF;
END

This is a different issue: you can optimize this procedure a bit. Why hit the datastore twice when one query will do? Just set the isDeleted attribute to 1 and check the row_count value afterwards.

BEGIN
  UPDATE aboutus SET isDeleted = 1 WHERE aboutUsId = p_id AND isDeleted = 0;
  IF (SELECT row_count()) <= 0 THEN
    SELECT 'No record to delete';
  END IF;
END

回复收藏 0 原文