CODEWITHSUNDEEP
sqljoinsql-update
Jeff
Jeff

Reputation: 449

Using a sql join to update a table

I am trying to update a table based on if a certain value in another table is '999'. Below is the code. I am crossing databases so I was a little confused on the syntax.

I am getting this error: SQL Error: Incorrect syntax near the keyword 'left'.

    declare @ProcessDate datetime
set @ProcessDate=getdate()

update ${BccrDatabase}..ReturnsHeader
  left join ${JdeDatabase}.f47011 poh on --Temp Header
    poh.SYEDOC = ReturnsHeader.DocumentNumber and poh.SYEDCT = 'EP'
  left join ${JdeDatabase}.f4201 soh on --Prod Header
    soh.SHKCOO = poh.SYEKCO 
    and soh.SHDOCO = poh.SYDOCO
    and soh.SHDCTO = poh.SYEDCT
  left join ${JdeDatabase}.f4211 sod on --Production Detail
    sod.SDKCOO = soh.SHKCOO
    and sod.SDDOCO = soh.SHDOCO
    and sod.SDDCTO = soh.SHDCTO 
  left join jde_crp.crpdta.f42119 hist on --History Detail
    hist.sddoco = soh.SHDOCO and
    hist.sdkcoo = soh.SHKCOO and
    hist.sddcto = soh.SHDCTO
 set
  SentToEdiDate=@ProcessDate
where 
  SentToEdiDate is null 
  and ApprovalStepID=4 -- Complete
  and DocumentBranchPlant=@BranchPlant
  and sod.SDNXTR is not null or hist.SDNXTR is not null 
  and sod.SDNXTR = '999' or hist.SDNXTR = '999'

Upvotes: 0

Views: 136

Answers (1)

RMalke
RMalke

Reputation: 4094

If I understand correctly your code should be like: 

declare @ProcessDate datetime
set @ProcessDate=getdate()

update ${BccrDatabase}..ReturnsHeader
set SentToEdiDate=@ProcessDate
FROM ${BccrDatabase}..ReturnsHeader
    left join ${JdeDatabase}.f47011 poh on --Temp Header
        poh.SYEDOC = ReturnsHeader.DocumentNumber and poh.SYEDCT = 'EP'
    left join ${JdeDatabase}.f4201 soh on --Prod Header
        soh.SHKCOO = poh.SYEKCO 
        and soh.SHDOCO = poh.SYDOCO
        and soh.SHDCTO = poh.SYEDCT
    left join ${JdeDatabase}.f4211 sod on --Production Detail
        sod.SDKCOO = soh.SHKCOO
        and sod.SDDOCO = soh.SHDOCO
        and sod.SDDCTO = soh.SHDCTO 
    left join jde_crp.crpdta.f42119 hist on --History Detail
        hist.sddoco = soh.SHDOCO and
        hist.sdkcoo = soh.SHKCOO and
        hist.sddcto = soh.SHDCTO
where 
    SentToEdiDate is null 
    and ApprovalStepID=4 -- Complete
    and DocumentBranchPlant=@BranchPlant
    and sod.SDNXTR is not null or hist.SDNXTR is not null 
    and sod.SDNXTR = '999' or hist.SDNXTR = '999'

The general structure is:

UPDATE a
    SET a.YourColumn = newValue
FROM Table1 AS a
JOIN Table2 AS b
    ON a.JoinColumn = b.JoinColumn
WHERE ...

Upvotes: 2

Related Questions