We help IT Professionals succeed at work.

Inner Join Update issues

kumarjcet
kumarjcet asked
on
hi getting issues on Update with inner join

UPDATE R_verificationData AS A INNER JOIN
(SELECT * FROM (SELECT * FROM
(select distinct
R_Project_Items_Cost.[Project Id],  
ISNULL(R_Project_Items_Cost.[PO #] ,'')as [PO #],
ISNULL(R_Project_Items_Cost.[Item Id] ,'') as [Packaging Item #],  
ISNULL(R_Project_Items_Cost.[TYPE OF PR],'')as [Process Classification],
ISNULL(R_Project_Items_Cost.[Qty Per],'')as [Packg.Item Pieces/Container],
ISNULL(R_Project_Items_Cost.[QTY],'')as [Packaging Item Total Reqd Qty],
'' as [Allow Duplicate Packaging Item],  
'system uploaded into web: -'+CONVERT(varchar(10),GETDATE(),101) as [PSE Comments],  
'' as Condition,  
'' as [Start Record],  
'' as [End Record],  
2 as [# of Initial Needs Schedules],  
1 as [# of Milestone Schedules],  
case when [R_Project_Items_Cost].T19_RCD  IS null then ''  else CONVERT(varchar(10),[R_Project_Items_Cost].T19_RCD,101)  end AS [First Article Date],  
case when [Tbl_PRItemDetail].[First Article Quantity] IS null then ''  else [Tbl_PRItemDetail].[First Article Quantity] end AS [First Article Quantity],  
case when [Tbl_PRItemDetail].[FA Req CSQ]=1 then 'X' else '' end as [FA NOT REQ. BY CSQ],  
case when [Tbl_PRItemDetail].InitialNeedsQuantity>0 THEN CONVERT(varchar(10),[Tbl_PRItemDetail].InitialNeedsDate ,101) ELSE ''  end AS [First Initial Needs Date],  
case when [Tbl_PRItemDetail].InitialNeedsQuantity  IS null then ''  else [Tbl_PRItemDetail].InitialNeedsQuantity end AS [First Initial Needs Quantity],  
case when [Tbl_PRItemDetail].[Second Initial Needs Date] IS null then '' else CONVERT(varchar(10),[Tbl_PRItemDetail].[Second Initial Needs Date] ,101) end as [Second Initial Needs Date],  
case when [Tbl_PRItemDetail].[Second Initial Needs Quantity] IS null then '' else [Tbl_PRItemDetail].[Second Initial Needs Quantity] end as [Second Initial Needs Quantity],  
case when [Tbl_PRItemDetail].DateRequiredComplete IS null then '' else CONVERT(varchar(10),[Tbl_PRItemDetail].DateRequiredComplete ,101) end as [RCD],  
ISNULL([T_PR].[AR #] ,'')as [AR #],
ISNULL(R_Project_Items_Cost.[EXPENDITURE],'')as [EXPENDITURE],  
case when R_Project_Items_Cost.[PO DATE] IS null then '' else CONVERT(varchar(10),R_Project_Items_Cost.[PO DATE] ,101) end as [PO DATE],  
R_Project_Items_Cost.[PR #]+'_'+convert(varchar,R_Project_Items_Cost.[PR SEQ]) as [PRC# Common Code],  
ISNULL(R_Project_Items_Cost.[Duns Number],'')as [Fabricator/Dunnage Supplier DUNS Number],
ISNULL(R_Project_Items_Cost.[Supplier],'')as [Fabricator/Dunnage Supplier],
ISNULL([Tbl_PRItemDetail].[supplierseq],'')as [PAS],
ISNULL([PSE_DUNS].[PSE],'')as [PSE],
'' as [Shipping Location],  
case when [Tbl_PRItemDetail].[supplierseq]='P' then R_Project_Items_Cost.[Sup Fab Puf Name]+T_PR.[DUNS] when [Tbl_PRItemDetail].[supplierseq]='A' then R_Project_Items_Cost.[Sup Fab Puf Name]+T_PR.[DUNS] else '' END as [Receiving Location],  
ISNULL(R_Project_Items_Cost.[Duns Number],'')as [Receiving Location Duns],
ISNULL(F_Data_FabricatorsInfo.[Cisco Code],'')as [CISCO Code],
ISNULL([Tbl_PRItemDetail].[Requesting Organization],'')as [Request Category],
ISNULL(R_Project_Items_Cost.[CAPITAL OR EXPENSE],'')as [Classification],  
'' as [Freight Charge To],  
'GMNAC STANDARD MATRIX' as [Shipping Mode],  
'' as [Generated],  
'Y' AS [Read]  
from  R_Project_Items_Cost  
left outer join Tbl_PRItemDetail
on R_Project_Items_Cost.[Project Id] = Tbl_PRItemDetail.ProjectID
and R_Project_Items_Cost.[Item Id] = Tbl_PRItemDetail.ItemNumber
left outer join PSE_DUNS
on R_Project_Items_Cost.DUNS = PSE_DUNS.DUNS
left outer join T_PR  
on R_Project_Items_Cost.[Project Id] = T_PR.[PROJECT ID #]
and R_Project_Items_Cost.[PR #] = T_PR.[PR #]
left outer join F_Data_FabricatorsInfo
on R_Project_Items_Cost.[Duns Number] = F_Data_FabricatorsInfo.[Duns Number]
WHERE R_Project_Items_Cost.[PO #] NOT IN ('DELETED','REJECTED') AND  [Tbl_PRItemDetail].[supplierseq] in('P','S','A') ) tbl
WHERE exists(select R_verificationData.[Project Id],R_verificationData.[Packaging Item NO] as [Packaging Item #],
R_verificationData.[PO NO] as [PO #],R_verificationData.Classification from R_verificationData
WHERE R_verificationData.[Project Id]=tbl.[Project Id] AND R_verificationData.[Packaging Item NO]=tbl.[Packaging Item #]
AND R_verificationData.[PO NO]=tbl.[PO #] and R_verificationData.[AR No]=tbl.[AR #])) AS   B)
ON
A.[project id] = B.[project id] AND
A.[PO NO] =B.[PO NO] AND
A.[Packaging Item NO] =B.[Packaging Item NO]
SET
 A.[project id] = B.[project id] ,A.[PO NO] =B.[PO NO]
,A.[Packaging Item NO] =B.[Packaging Item NO]
,A.[Process Classification] =B.[Process Classification]
,A.[Packg#Item Pieces/Container] =B.[Packg#Item Pieces/Container]
,A.[Packaging Item Total Reqd Qty] =B.[Packaging Item Total Reqd Qty]
,A.[Allow Duplicate Packaging Item] =B.[Allow Duplicate Packaging Item]
,A.[PSE Comments] =B.[PSE Comments]
,A.[Condition] =B.[Condition]
,A.[Start Record] =B.[Start Record]
,A.[End Record] =B.[End Record]
,A.[# of Initial Needs Schedules] =B.[# of Initial Needs Schedules]
,A.[# of Milestone Schedules] =B.[# of Milestone Schedules]
,A.[First Article Date] =B.[First Article Date]
,A.[First Article Quantity] =B.[First Article Quantity]
,A.[FA NOT REQ# BY CSQ] =B.[FA NOT REQ# BY CSQ]
,A.[First Initial Needs Date] =B.[First Initial Needs Date]
,A.[First Initial Needs Quantity] =B.[First Initial Needs Quantity]
,A.[Second Initial Needs Date] =B.[Second Initial Needs Date]
,A.[Second Initial Needs Quantity] =B.[Second Initial Needs Quantity]
,A.[RCD] =B.[RCD]
,A.[AR No] =B.[AR No]
,A.[EXPENDITURE] =B.[EXPENDITURE]
,A.[PO DATE]B.[PO DATE]
,A.[PRC Common Code] =B.[PRC Common Code]
,A.[Fabricator/Dunnage Supplier DUNS Number] =B.[Fabricator/Dunnage Supplier DUNS Number]
,A.[Fabricator/Dunnage Supplier] =B.[Fabricator/Dunnage Supplier]
,A.[PAS] =B.[PAS]
,A.[PSE] =B.[PSE]
,A.[Shipping Location] =B.[Shipping Location]
,A.[Receiving Location] =B.[Receiving Location]
,A.[Receiving Location Duns] =B.[Receiving Location Duns]
,A.[CISCO Code] =B.[CISCO Code]
,A.[Request Category] =B.[Request Category]
,A.[Classification] =B.[Classification]
,A.[Freight Charge To] =B.[Freight Charge To]
,A.[Shipping Mode] =B.[Shipping Mode]
WHERE A.[project id] = B.[project id]
AND A.[PO NO] =B.[PO NO]
AND A.[Packaging Item NO] =B.[Packaging Item NO]
AND A.[AR No] =B.[AR No]
end


Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 59
Incorrect syntax near the keyword 'ON'.
Comment
Watch Question

Check this dude

UPDATE A SET
 A.[project id] = B.[project id] ,A.[PO NO] =B.[PO NO]
,A.[Packaging Item NO] =B.[Packaging Item NO]
,A.[Process Classification] =B.[Process Classification]
,A.[Packg#Item Pieces/Container] =B.[Packg#Item Pieces/Container]
,A.[Packaging Item Total Reqd Qty] =B.[Packaging Item Total Reqd Qty]
,A.[Allow Duplicate Packaging Item] =B.[Allow Duplicate Packaging Item]
,A.[PSE Comments] =B.[PSE Comments]
,A.[Condition] =B.[Condition]
,A.[Start Record] =B.[Start Record]
,A.[End Record] =B.[End Record]
,A.[# of Initial Needs Schedules] =B.[# of Initial Needs Schedules]
,A.[# of Milestone Schedules] =B.[# of Milestone Schedules]
,A.[First Article Date] =B.[First Article Date]
,A.[First Article Quantity] =B.[First Article Quantity]
,A.[FA NOT REQ# BY CSQ] =B.[FA NOT REQ# BY CSQ]
,A.[First Initial Needs Date] =B.[First Initial Needs Date]
,A.[First Initial Needs Quantity] =B.[First Initial Needs Quantity]
,A.[Second Initial Needs Date] =B.[Second Initial Needs Date]
,A.[Second Initial Needs Quantity] =B.[Second Initial Needs Quantity]
,A.[RCD] =B.[RCD]
,A.[AR No] =B.[AR No]
,A.[EXPENDITURE] =B.[EXPENDITURE]
,A.[PO DATE] = B.[PO DATE]
,A.[PRC Common Code] =B.[PRC Common Code]
,A.[Fabricator/Dunnage Supplier DUNS Number] =B.[Fabricator/Dunnage Supplier DUNS Number]
,A.[Fabricator/Dunnage Supplier] =B.[Fabricator/Dunnage Supplier]
,A.[PAS] =B.[PAS]
,A.[PSE] =B.[PSE]
,A.[Shipping Location] =B.[Shipping Location]
,A.[Receiving Location] =B.[Receiving Location]
,A.[Receiving Location Duns] =B.[Receiving Location Duns]
,A.[CISCO Code] =B.[CISCO Code]
,A.[Request Category] =B.[Request Category]
,A.[Classification] =B.[Classification]
,A.[Freight Charge To] =B.[Freight Charge To]
,A.[Shipping Mode] =B.[Shipping Mode]

FROM R_verificationData AS A
INNER JOIN  (SELECT * FROM
                              (SELECT * FROM
                                    (select distinct
                                          R_Project_Items_Cost.[Project Id] ,  
                                          ISNULL(R_Project_Items_Cost.[PO #] ,'')as [PO #],
                                          ISNULL(R_Project_Items_Cost.[Item Id] ,'') as [Packaging Item #],  
                                          ISNULL(R_Project_Items_Cost.[TYPE OF PR],'')as [Process Classification],
                                          ISNULL(R_Project_Items_Cost.[Qty Per],'')as [Packg.Item Pieces/Container],
                                          ISNULL(R_Project_Items_Cost.[QTY],'')as [Packaging Item Total Reqd Qty],
                                          '' as [Allow Duplicate Packaging Item],  
                                          'system uploaded into web: -'+CONVERT(varchar(10),GETDATE(),101) as [PSE Comments],  
                                          '' as Condition,  
                                          '' as [Start Record],  
                                          '' as [End Record],  
                                          2 as [# of Initial Needs Schedules],  
                                          1 as [# of Milestone Schedules],  
                                          case when [R_Project_Items_Cost].T19_RCD  IS null then ''  else CONVERT(varchar(10),[R_Project_Items_Cost].T19_RCD,101)  end AS [First Article Date],  
                                          case when [Tbl_PRItemDetail].[First Article Quantity] IS null then ''  else [Tbl_PRItemDetail].[First Article Quantity] end AS [First Article Quantity],  
                                          case when [Tbl_PRItemDetail].[FA Req CSQ]=1 then 'X' else '' end as [FA NOT REQ. BY CSQ],  
                                          case when [Tbl_PRItemDetail].InitialNeedsQuantity>0 THEN CONVERT(varchar(10),[Tbl_PRItemDetail].InitialNeedsDate ,101) ELSE ''  end AS [First Initial Needs Date],  
                                          case when [Tbl_PRItemDetail].InitialNeedsQuantity  IS null then ''  else [Tbl_PRItemDetail].InitialNeedsQuantity end AS [First Initial Needs Quantity],  
                                          case when [Tbl_PRItemDetail].[Second Initial Needs Date] IS null then '' else CONVERT(varchar(10),[Tbl_PRItemDetail].[Second Initial Needs Date] ,101) end as [Second Initial Needs Date],  
                                          case when [Tbl_PRItemDetail].[Second Initial Needs Quantity] IS null then '' else [Tbl_PRItemDetail].[Second Initial Needs Quantity] end as [Second Initial Needs Quantity],  
                                          case when [Tbl_PRItemDetail].DateRequiredComplete IS null then '' else CONVERT(varchar(10),[Tbl_PRItemDetail].DateRequiredComplete ,101) end as [RCD],  
                                          ISNULL([T_PR].[AR #] ,'')as [AR #],
                                          ISNULL(R_Project_Items_Cost.[EXPENDITURE],'')as [EXPENDITURE],  
                                          case when R_Project_Items_Cost.[PO DATE] IS null then '' else CONVERT(varchar(10),R_Project_Items_Cost.[PO DATE] ,101) end as [PO DATE],  
                                          R_Project_Items_Cost.[PR #]+'_'+convert(varchar,R_Project_Items_Cost.[PR SEQ]) as [PRC# Common Code],  
                                          ISNULL(R_Project_Items_Cost.[Duns Number],'')as [Fabricator/Dunnage Supplier DUNS Number],
                                          ISNULL(R_Project_Items_Cost.[Supplier],'')as [Fabricator/Dunnage Supplier],
                                          ISNULL([Tbl_PRItemDetail].[supplierseq],'')as [PAS],
                                          ISNULL([PSE_DUNS].[PSE],'')as [PSE],
                                          '' as [Shipping Location],  
                                          case when [Tbl_PRItemDetail].[supplierseq]='P' then R_Project_Items_Cost.[Sup Fab Puf Name]+T_PR.[DUNS] when [Tbl_PRItemDetail].[supplierseq]='A' then R_Project_Items_Cost.[Sup Fab Puf Name]+T_PR.[DUNS] else '' END as [Receiving Location],  
                                          ISNULL(R_Project_Items_Cost.[Duns Number],'')as [Receiving Location Duns],
                                          ISNULL(F_Data_FabricatorsInfo.[Cisco Code],'')as [CISCO Code],
                                          ISNULL([Tbl_PRItemDetail].[Requesting Organization],'')as [Request Category],
                                          ISNULL(R_Project_Items_Cost.[CAPITAL OR EXPENSE],'')as [Classification],  
                                          '' as [Freight Charge To],  
                                          'GMNAC STANDARD MATRIX' as [Shipping Mode],  
                                          '' as [Generated],  
                                          'Y' AS [Read]  
                                    from  R_Project_Items_Cost  
                                          left outer join Tbl_PRItemDetail  on R_Project_Items_Cost.[Project Id] = Tbl_PRItemDetail.ProjectID
                                                                                                and R_Project_Items_Cost.[Item Id] = Tbl_PRItemDetail.ItemNumber
                                          left outer join PSE_DUNS on R_Project_Items_Cost.DUNS = PSE_DUNS.DUNS
                                          left outer join T_PR  on R_Project_Items_Cost.[Project Id] = T_PR.[PROJECT ID #]
                                                                                          and R_Project_Items_Cost.[PR #] = T_PR.[PR #]
                                          left outer join F_Data_FabricatorsInfo on R_Project_Items_Cost.[Duns Number] = F_Data_FabricatorsInfo.[Duns Number]
                                    WHERE R_Project_Items_Cost.[PO #] NOT IN ('DELETED','REJECTED') AND  [Tbl_PRItemDetail].[supplierseq] in('P','S','A') ) tbl
                              WHERE exists(
                                                select R_verificationData.[Project Id],R_verificationData.[Packaging Item NO] as [Packaging Item #],
                                                                              R_verificationData.[PO NO] as [PO #],R_verificationData.Classification
                                                      from R_verificationData
                                                      WHERE R_verificationData.[Project Id]=tbl.[Project Id] AND R_verificationData.[Packaging Item NO]=tbl.[Packaging Item #]
                                                      AND R_verificationData.[PO NO]=tbl.[PO #] and R_verificationData.[AR No]=tbl.[AR #])
                              ) as tbl1
                        )  AS B ON A.[project id] = B.[project id] AND
A.[PO NO] =B.[PO NO] AND A.[Packaging Item NO] =B.[Packaging Item NO]

WHERE A.[project id] = B.[project id]
AND A.[PO NO] =B.[PO NO]
AND A.[Packaging Item NO] =B.[Packaging Item NO]
AND A.[AR No] =B.[AR No]

Author

Commented:
Thanks a lot santhimurthyd: its resolved my issues

Author

Commented:
hi santhimurthyd:

i have one more query...

i need to update status only for Modified rows in the same above query...after update
'' as [Generated],   'Y' AS [Read](there is no unique fields more then 5 fields need to consider)...
how to find only modified rows in this R_verificationData and need to update only that row status

Any solution for this...



 
You can use Trigger on row Update of the table
Check for the below Samples

http://stackoverflow.com/questions/1249061/update-function-in-tsql-trigger
http://msdn.microsoft.com/en-us/library/ms187326%28SQL.90%29.aspx
Let you share the columns needs to be updated / modified after updating the Record.

If possible you can raise this as separate querry
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
for your information, a UPDATE/JOIN article you might want to read:
http://www.experts-exchange.com/A_1517.html

Author

Commented:
resolved