以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  [求助] SQL语句求助  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=121317)
--  作者：2900819580
--  发布时间：2018/7/3 20:38:00
--  [求助] SQL语句求助

Dim tb As Table = Tables(e.Form.Name & "_Table1")
tb.Fill("sele ct * fr om {物料表} where ( 单位 Is null Or 默认仓库名称 Is null )", "ksg", False)
tb.SetColVisibleWidth("代码|154|名称|204|规格型号|135|单位|36|备注|262|类别|130|类型|59|默认仓库名称|98")
For Each r As Row In tb.Rows
   
    Dim cmd As new SQLCommand
   
    cmd.C   
    cmd.CommandText = "select * fr om {t_ICItemCore} where FNumber = \'" & r("代码") & "\'"
    Dim dt As DataTable = cmd.ExecuteReader
   
    If dt.DataRows.Count > 0 Then
        Dim dr As DataRow = dt.DataRows(0)
       
        cmd.CommandText = "select * fr om {t_ICItemBase} where FItemID = \'" & dr("FItemID") & "\'"
        Dim dt1 As DataTable = cmd.Ex ecuteReader
        Dim dr1 As DataRow = dt1.DataRows(0)
       
        If dr1("FDefaultLoc") <> "0" Then
            cmd.CommandText = "select * fr om {t_Stock} where FItemID = \'" & dr1("FDefaultLoc") & "\'"
            Dim dt2 As DataTable = cmd.ExecuteReader
            Dim dr2 As DataRow = dt2.DataRows(0)
            R("默认仓库名称") = dr2("FName")
        End If
       
        cmd.CommandText = "select * fr om {t_MeasureUnit} where FItemID = \'" & dr1("FUnitID") & "\'"
        Dim dt3 As DataTable = cmd.ExecuteReader
        Dim dr3 As DataRow = dt3.DataRows(0)
        R("单位") = dr3("FName")
       
        cmd.CommandText = "select * fro m {t_ICItemMaterial} where FItemID = \'" & dr("FItemID") & "\'"
        Dim dt4 As DataTable = cmd.ExecuteReader
        Dim dr4 As DataRow = dt4.DataRows(0)
      
        R("备注") = dr4("Fnote")
    Else
        R("单位") = "无用"
        R("默认仓库名称") = "无用"
    End If
Next
tb.DataTable.Save
MessageBox.Show("物料更新完成")

 

 

以上代码 能不能用SQL语句直接查找出来。

[此贴子已经被作者于2018/7/3 20:38:59编辑过]
--  作者：有点蓝
--  发布时间：2018/7/3 21:41:00
--  
试试：

update 物料表 set 单位=\'无用\',默认仓库名称=\'无用\' from 物料表 where (单位 Is null Or 默认仓库名称 Is null) and not exists( select FNumber from t_ICItemCore where t_ICItemCore.FNumber = 物料表.代码)

update 物料表 set 默认仓库名称= c.FName from 物料表  As w Inner join t_ICItemCore As a on w.代码 = a.FNumber Inner join t_ICItemBase As b on a.FItemID = b.FItemID Inner join t_Stock As c on b.FDefaultLoc = c.FItemID where (w.单位 Is null Or w.默认仓库名称 Is null) and b.FDefaultLoc <> 0

还原一个测试库测试