百胜 bs3000+ 更改款式 尺码的SQL语句

 select  TOP 100 PERCENT SPGG1.SPDM as SPDM, SPGG1.GGDM as GGDM, GUIGE1.GGDM as  GGDM_SUB, GUIGE1.GGMC as  GGMC_SUB, SPGG1.BYZD1 as BYZD1 from SPGG1 as SPGG1, GUIGE1 as GUIGE1 where SPGG1.GGDM =  GUIGE1.GGDM and SPDM = 'Q122410238A'
go

 select  TOP 100 PERCENT SPGG2.SPDM as SPDM, SPGG2.GGDM as GGDM, GUIGE2.GGDM as  GGDM_SUB, GUIGE2.GGMC as  GGMC_SUB from SPGG2 as SPGG2, GUIGE2 as GUIGE2 where SPGG2.GGDM =  GUIGE2.GGDM and SPDM = 'Q122410238A'

--尺码规则 GUIGE2.GGDM 尺码代码，GUIGE2.GGMC 尺码名称
SELECT * FROM GUIGE2
--颜色规则
SELECT * FROM GUIGE1
SELECT * FROM SPGG2

select Name from sysobjects where xtype='u' and status>=0 order by name

 

go
 select  TOP 100 PERCENT ShangPinTM.SPDM as SPDM, ShangPinTM.bPrint as bPrint, ShangPinTM.TMID as TMID, vw_TM.TMID as  TMID_SUB, vw_TM.BYZD1 as  BYZD1_SUB from ShangPinTM as ShangPinTM, vw_TM as vw_TM where ShangPinTM.TMID =  vw_TM.TMID and SPDM = 'Q122410238A'
go
--商品的的打印规则的设置 ShangPinTM.TMID  是规则设置
SELECT * FROM ShangPinTM
--编码规则
SELECT * FROM vw_TM

 select  TOP 100 PERCENT ShangKeHu.SPDM as SPDM, ShangKeHu.KHDM as KHDM, KEHU.KHDM as  KHDM_SUB, KEHU.KHMC as  KHMC_SUB from ShangKeHu as ShangKeHu, KEHU as KEHU where ShangKeHu.KHDM =  KEHU.KHDM and SPDM = 'Q122410238A'
go
 select  TOP 100 PERCENT ShangCheckGoal.SPDM as SPDM, ShangCheckGoal.KHDM as KHDM, KEHU.KHDM as  KHDM_SUB, KEHU.KHMC as  KHMC_SUB, ShangCheckGoal.iGoal as iGoal from ShangCheckGoal as ShangCheckGoal, KEHU as KEHU where ShangCheckGoal.KHDM =  KEHU.KHDM and SPDM = 'Q122410238A'
go
SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go

--找出基础资料的所有数据库
select Name from sysobjects where xtype='u' and status>=0 order by name
--查出 Q%A，B,C,D的款
 select  TOP 100 PERCENT SPGG2.SPDM as SPDM, SPGG2.GGDM as GGDM, GUIGE2.GGDM as  GGDM_SUB, GUIGE2.GGMC as  GGMC_SUB from SPGG2 as SPGG2, GUIGE2 as GUIGE2 where SPGG2.GGDM =  GUIGE2.GGDM and SPDM = 'Q122410238A'

SELECT * FROM SHANGPIN WHERE ZJF LIKE '2%' AND SPDM LIKE 'Q%'

--要改尺码的顺序（倒着走）
--Q122220552A
-- 三个条件： 表名，款号，尺码

第一步：款式资料
--查出 Q%A，B,C,D的款
 select  TOP 100 PERCENT SPGG2.SPDM as SPDM, SPGG2.GGDM as GGDM, GUIGE2.GGDM as  GGDM_SUB, GUIGE2.GGMC as  GGMC_SUB from SPGG2 as SPGG2, GUIGE2 as GUIGE2 where SPGG2.GGDM =  GUIGE2.GGDM and SPDM = 'Q122410238A'
 select  TOP 100 PERCENT SPGG2.SPDM as SPDM, SPGG2.GGDM as GGDM, GUIGE2.GGDM as  GGDM_SUB, GUIGE2.GGMC as  GGMC_SUB from SPGG2 as SPGG2, GUIGE2 as GUIGE2 where SPGG2.GGDM =  GUIGE2.GGDM and SPDM LIKE 'Q%A'
SELECT * FROM SPGG2 WHERE SPDM LIKE 'Q%A'
SELECT * FROM SPGG2 WHERE SPDM='Q122220552A';
SELECT * FROM SPGG2 WHERE SPDM='Q122220552B';

UPDATE SPGG2 SET GGDM='08' WHERE SPDM='Q122220552B' AND GGDM='01';
UPDATE SPGG2 SET GGDM='09' WHERE SPDM='Q122220552B' AND GGDM='02';
UPDATE SPGG2 SET GGDM='10' WHERE SPDM='Q122220552B' AND GGDM='03';
UPDATE SPGG2 SET GGDM='11' WHERE SPDM='Q122220552B' AND GGDM='04';
UPDATE SPGG2 SET GGDM='12' WHERE SPDM='Q122220552B' AND GGDM='05';

--颜色
SELECT * FROM SPGG1 WHERE GGDM ='100';
UPDATE SPGG1 SET GGDM='38' WHERE  GGDM ='100';

SELECT * FROM SPGG1 WHERE GGDM ='100';

1-1
SPGG2
SPDM
GGDM

第二部：定价单

第三部：入库单
--入库订单
SELECT * FROM JORDERMX
SELECT * FROM JORDERMX WHERE SPDM LIKE 'Q%A';
SELECT * FROM JORDERMX WHERE SPDM='Q122220552B' AND GG2DM='08';
UPDATE JORDERMX SET GG2DM='08' WHERE GG2DM='12' AND  SPDM LIKE 'Q122220552A';
UPDATE JORDERMX SET GG2DM='09' WHERE GG2DM='13' AND  SPDM LIKE 'Q122220552A';
UPDATE JORDERMX SET GG2DM='10' WHERE GG2DM='14' AND  SPDM LIKE 'Q122220552A';
UPDATE JORDERMX SET GG2DM='11' WHERE GG2DM='15' AND  SPDM LIKE 'Q122220552A';
UPDATE JORDERMX SET GG2DM='12' WHERE GG2DM='16' AND  SPDM LIKE 'Q122220552A';

--颜色
SELECT * FROM JORDERMX WHERE GG2DM='100';

3-1
JORDERMX
SPDM
GG2DM

--入库通知单
SELECT * FROM JSENDMX WHERE SPDM like 'Q%A'
SELECT * FROM JSENDMX WHERE SPDM='Q122220552A';
3-2
JSENDMX
SPDM
GG2DM

--入库单
SELECT * FROM SPJHDMX WHERE SPDM like 'Q%A'
SELECT * FROM SPJHDMX WHERE SPDM='Q122410238A';
3-3
SPJHDMX
SPDM
GG2DM

第四步：订单和发货单
--调拨订单
SELECT * FROM DBJRDMX WHERE SPDM like 'Q%A';
SELECT * FROM DBJRDMX WHERE SPDM='Q122220552A';
--UPDATE DBJRDMX SET GG2DM='01' WHERE GG2DM='12' AND SPDM like 'Q%A';
UPDATE DBJRDMX SET GG2DM='08' WHERE GG2DM='01' AND SPDM like 'Q122220552B';
UPDATE DBJRDMX SET GG2DM='09' WHERE GG2DM='02' AND SPDM like 'Q122220552B';
UPDATE DBJRDMX SET GG2DM='10' WHERE GG2DM='03' AND SPDM like 'Q122220552B';
UPDATE DBJRDMX SET GG2DM='11' WHERE GG2DM='04' AND SPDM like 'Q122220552B';
UPDATE DBJRDMX SET GG2DM='12' WHERE GG2DM='05' AND SPDM like 'Q122220552B';

4-1
DBJRDMX
SPDM
GG2DM

--调拨通知单
SELECT * FROM DSENDMX WHERE SPDM like 'Q%B'
SELECT * FROM DSENDMX WHERE SPDM='Q122220552A';
4-2
DSENDMX
SPDM
GG2DM

--调拨发货单
SELECT * FROM QDDBDMX WHERE SPDM like 'Q%A'
SELECT * FROM QDDBDMX WHERE SPDM='Q122410238A';
4-3
QDDBDMX
SPDM
GG2DM

 

--配货订单
SELECT * FROM PHJRDMX WHERE SPDM like 'Q%B'
SELECT * FROM PHJRDMX WHERE SPDM='Q122220552B';
UPDATE PHJRDMX SET GG2DM='08' WHERE GG2DM='01' AND SPDM like 'Q122220552A';
UPDATE PHJRDMX SET GG2DM='09' WHERE GG2DM='02' AND SPDM like 'Q122220552A';
UPDATE PHJRDMX SET GG2DM='10' WHERE GG2DM='03' AND SPDM like 'Q122220552A';
UPDATE PHJRDMX SET GG2DM='11' WHERE GG2DM='04' AND SPDM like 'Q122220552A';
UPDATE PHJRDMX SET GG2DM='12' WHERE GG2DM='05' AND SPDM like 'Q122220552A';
--配货通知单
SELECT * FROM PSENDMX WHERE SPDM like 'Q%A'
SELECT * FROM PSENDMX WHERE SPDM='Q122220552B';
--配货发货单
SELECT * FROM SDPHDMX WHERE SPDM like 'Q%A'
SELECT * FROM SDPHDMX WHERE SPDM='Q122410238A';