数据库DBA中有表Table
结构如下:
-------------
id(自增)
path
---------------
path的内容类似这样记录
------------------------------------200410172013291.jpg
2004101516293326425686.jpg
200412312558clip_image002.jpg
200410141821208.jpg
2004101213222695B6C65F4468C5E0D70242E7C066F154.jpg
20041011163730IMG_0243.JPG
2005720145516fyzw_20057138345.jpg
200578221362005620164840.jpg
20057822520玉照.JPG
20057151752442222.jpg
200578203732英x.jpg
200578164326图片 008.jpg
200581832240.jpg
2005781626123.jpg
20057815392112.jpg
20057815324211111 拷贝.jpg
2004101212359vb2.JPG
2004108134415IMAGE_00482.jpg
2004925232010照片:).jpg
2004106143316t.jpg
20049231033373.jpg
2004922133386.JPG
2004914124416showphoto.jpg
2004911223336photo28.jpg
2004927945224.jpg
2005112819271273499.jpg
2004830144020ldmsapp1.jpg
2005715113135照片 074_调整大小.jpg
……
--------------------------------- 有一图片文件夹,里面图片名称就是path的值
(说明:图片文件夹中存在的图片,一定可以在数据库中找到记录)有什么好方法?如何可以快速地将无图片的path值填为null呢???
图片大约有1000个左右
path中的的记录大约为5万条,
然后与你的表join一下..没有找到的..UPDATE为null;
DELETE #tmp WHERE filename IS NULL;DELETE A
FROM tb AS A
LEFT JOIN #tmp AS B
ON A.filename = B.filename
WHERE A.filename IS NULL;DROP TABLE #tmp;
DELETE #tmp WHERE filename IS NULL;UPDATE A SET
filename = null
FROM tb AS A
LEFT JOIN #tmp AS B
ON A.filename = B.filename
WHERE A.filename IS NULL;DROP TABLE #tmp;
drop table tb
go
create table tb(id int,path varchar(200))
insert into tb
select 1,'F:\相片集\二姐结婚照\aa.jpg'
union all
select 2,'F:\相片集\二姐结婚照\DSCF3831.JPG'select * from tbdrop table #t
CREATE TABLE #t(filename NVARCHAR(1024));insert into #t
EXEC master.dbo.xp_cmdshell 'dir F:\相片集\结婚照/b'update tb
set path=null
from tb left join #t t
on tb.path like '%'+t.filename+'%'
where filename is nullselect * from tb
DELETE #tmp WHERE filename IS NULL;--这样能显示记录吗?SELECT * FROM #tmp;SELECT * FROM #tmp AS A
JOIN tb AS B
ON A.filename = B.path;
CREATE TABLE #tmp(photofilename NVARCHAR(1024));
INSERT #tmp EXEC master.dbo.xp_cmdshell 'dir V:\wwwroot\UploadFiles\Person\PhotoFile /b';
DELETE #tmp WHERE photofilename IS NULL;--查看临时表
--select * from #tmpupdate A
set photourl=null
from bst_person_basic AS A left join #tmp B
on A.photourl like '%'+B.photofilename+'%'
where photofilename is null非常感谢各位的热心回复!!!