如何从查询更新列 (selmanufacturers_id)

Question

SELECT 
    'S. J. & G. FAZUL ELLAHIE (PVT) LTD.' AS CompanyName,
    'E - 46, S.I.T.E., KARACHI - 75700' AS CompanyAddress,
    'admin' login_user, 'crm.sjg.local' selhost,
    'admin' sellogin, 'sr_salereport' seltoprint,
    'SALES REPORT' selreportTitle, '' selflagType,
    'SI' selreportType, '' selfrmDate, '' seltoDate,
    '' selfromProductId, '' selac_fromProductId_ac,
    '' selbrandId, '' selcustomerId, '' selac_customerId_ac,
    '' selgodownId, '' selsmId, '' selsalesmanId, '' selcity,
    '' selarea, '' selorders_status, 'S' seldst,
    'Invoice' selgroup1, '1' selcurrencyid, 'id' seltype,
    'D' seldefUnit, '' selmanufacturers_id, 'Report' selyt0,
    CAST(docTypeId as varchar(20)) + CAST(documentId as varchar(20)) groupId1,
    [dbo].[ITL_DATE_TO_STRING_FOR_SORTING](refDate) groupTitle1, 
    '' groupId2, '' selgroup2,'' groupTitle2, * 
FROM 
    [dbo].SR_Sale(null, null, 0, 0, 0, 0, N'', N'', 0, N'SI', 0, 0, 1, N'id', N'D') AS TR 
WHERE
    docType IN ('SI', 'GYM RECEIPT')

Answer 1

这是一个常规更新语句

FROM dbo.SR_Sale
SET selmanufacturers_id ='xxx'
WHERE (TODO: add where-clause here to specify which row needs to be updated)

Answer 2

假设 selmanufacturers_id 在 [dbo] 中不存在.SR_Sale但与products_id相反。

    IF (OBJECT_ID('tempdb..#tmp_SR_SALE') IS NOT NULL )
    BEGIN
        DROP TABLE #tmp_SR_SALE
    END

    SELECT  'S. J. & G. FAZUL ELLAHIE (PVT) LTD.' AS CompanyName ,
            'E - 46, S.I.T.E., KARACHI - 75700' AS CompanyAddress ,
            'admin' login_user ,
            'crm.sjg.local' selhost ,
            'admin' sellogin ,
            'sr_salereport' seltoprint ,
            'SALES REPORT' selreportTitle ,
            '' selflagType ,
            'SI' selreportType ,
            '' selfrmDate ,
            '' seltoDate ,
            '' selfromProductId ,
            '' selac_fromProductId_ac ,
            '' selbrandId ,
            '' selcustomerId ,
            '' selac_customerId_ac ,
            '' selgodownId ,
            '' selsmId ,
            '' selsalesmanId ,
            '' selcity ,
            '' selarea ,
            '' selorders_status ,
            'S' seldst ,
            'Invoice' selgroup1 ,
            '1' selcurrencyid ,
            'id' seltype ,
            'D' seldefUnit ,
            '' selmanufacturers_id ,
            'Report' selyt0 ,
            CAST(docTypeId AS VARCHAR(20)) + CAST(documentId AS VARCHAR(20)) groupId1 ,
            [dbo].[ITL_DATE_TO_STRING_FOR_SORTING](refDate) groupTitle1 ,
            '' groupId2 ,
            '' selgroup2 ,
            '' groupTitle2 ,
            *
    INTO #tmp_SR_SALE FROM    [dbo].SR_Sale(NULL, NULL, 0, 0, 0, 0, N'', N'', 0, N'SI', 0, 0, 1,
                          N'id', N'D') AS TR
    WHERE   docType IN ( 'SI', 'GYM RECEIPT' )
    UPDATE #tmp_SR_SALE
    SET selmanufacturers_id = 'value here'
    WHERE products_id = 9031
    SELECT * FROM #tmp_SR_SALE