sql语句出现问题

来源：百度知道 编辑：UC知道 时间：2024/05/30 23:20:25
删除数据表相同值的问题

现在要删除 message 表中 user 值相同的项，语句如下：

$sql="delete from `message` where `user` in (
select `user` from `message` a.
( select `user` from `message` group by `user` having count(*)>1) b.
(where `a.user`=`b.user`)
)";

@mysql_query($sql) or die (mysql_error());

执行时提示如下错误：

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 '. ( select `user` from `message` group by `user` having count(*)>1) b. (w' at line 2

还有第二个问题：以下两个sql语句能不能合成一个sql语句？据说分开写效率不高。（数据表删除一条id后，后边的ID可以自动连续下来）

$d="delete from `message` where `id`=9" ;
$u="update `message` set `id`=`id`-1 where `id`>9";

第一个问题：sql语句换成这个试试：
sql=="delete from `message` where `user` in (select `user` from `message` group by `user` having count(*)>1)
第二个问题：
俩个语句合在一起？不明白你的意思。不过你可以定义一个事务来执行这两条语句啊。

上面的触发器是我写的，给分吧

比如表的构造如message (id,msg)这样
create triiger t ON message
instead of delete
AS
begin
declare @id int
select @id=id from deleted
delete message where id=@id
update message set id=id-1 where id>@id
end

删除相同数据的问题

delete from message where user in
(select user from
(select user from message) a,
(select user,count(user) from message group by user having count(user)>1) b
where a.user=b.user);

sql="delete from 'message' where 'user' into''