CODEWITHSUNDEEP
sqlsql-server-2008
Matt
Matt

Reputation: 358

Update 2nd Table Based Upon Rows Updated In First Table

I have two tables jhead and jjops. I am updating jhead with the following statement:

UPDATE jhead h
SET h.jhpcmp = 1
WHERE h.jhpcmp = '0' AND h.jhpqty <= h.jhqtys

But now I want to update jjops based upon what I updated in jhead. The statement would be like:

UPDATE jjops j
SET j.jascmp = 1, japcmp = 1
WHERE (This is where I am stuck)

What links the two tables is the following: h.jhjob = j.jajob

Is there some way to update both of the tables together? Should I first update table jjops using a join and then update table jhead and if so what Join should I use?

Upvotes: 0

Views: 69

Answers (1)

Laurence
Laurence

Reputation: 10976

The way to update two tables "at the same time" is to use a transaction. You can use the output clause as one way to pass stuff from one statement to the next. SQL Server also has a special syntax for joining in an update statement (see the second update)

Declare @ids table (jhjob int not null) -- replace with correct data type

Begin Transaction

Update 
    jhead
Set 
    jhpcmp = 1
output 
    inserted.jhjob into @ids
Where 
    jhpcmp = '0' And
    jhpqty <= jhqtys

Update 
    jjops
Set
    jascmp = 1,
    japcmp = 1
From
    jjops j
        inner join
    @ids h 
        on j.jajob = h.jhjob

Commit Transaction

Upvotes: 1

Related Questions