begin
declare name varchar(70);
declare insert_name cursor for select distinct s_name from supplier_name;delete from supplier_name where s_name is null or s_name ='';
open insert_name;
fetch insert_name into name;
while(name in not null) doinsert into supplier_data_copy (s_name) select distinct s_name from supplier_name;
end while;
close insert_name;
truncate supplier_name;
end
为什么会说语法错误的呀?insert into supplier_data_copy (s_name) select distinct s_name from supplier_name;说这里有错误
declare name varchar(70);
declare insert_name cursor for select distinct s_name from supplier_name;delete from supplier_name where s_name is null or s_name ='';
open insert_name;
fetch insert_name into name;
while(name in not null) doinsert into supplier_data_copy (s_name) select distinct s_name from supplier_name;
end while;
close insert_name;
truncate supplier_name;
end
为什么会说语法错误的呀?insert into supplier_data_copy (s_name) select distinct s_name from supplier_name;说这里有错误
-> truncate supplier_name;
-> end
-> //
ERROR 1064 (42000): 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 'not n
ull) do insert into supplier_data_copy (s_name) select distinct s_name fr' at li
ne 10
mysql> delimiter ;
mysql>
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
需要表中有唯一标识的字段,比如ID,保留ID最大 OR 最小的记录
那个id是自增的,然后我想把s_name相同的行只保留一条
我在网上找到了这样的代码:
但是有些地方看不懂,而且还是有错误的。
你帮我看下好吗?
BEGIN
declare @max integer,@id integer
declare cur_rows cursor local for select s_name,count(*) from supplier_data group by s_name having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from supplier_data where s_name = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0
END
delete a from supplier_data a inner join supplier_data b
on a.sname=b.sname and a.id>b.id;
select s_name,min(id) from supplier_name group by s_name) b using (s_name)
where b.s_name is null
谢谢 可以了,还有我想问下那个id可以重新排列吗?因为删了之后那个id是不连续的
OR
删除自增ID,再增加自增ID
可以重排,但不推荐。
select * from tt1 where s_name not in(一批数据)
declare @name varchar(70)
--声明一个游标insert_name
declare insert_name cursor
for
select distinct s_name from supplier_namedelete from supplier_name where s_name is null or s_name =''
--打开游标insert_name
open insert_name
--循环并提取游标的记录
fetch next insert_name into @name
while(@@fetch_status=0)
--此处写执行代码close insert_name
deallocate insert_name
GO--此处写select语句显示最终结果go