0

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?

Matt
  • 358
  • 3
  • 9
  • 23

1 Answers1

1

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
Laurence
  • 10,896
  • 1
  • 25
  • 34
  • What is @ids? Is that a temporary table? – Matt Sep 12 '13 at 23:16
  • It's a table variable, it's similar to a temporary table, although they have different semantics - see http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server – Laurence Sep 12 '13 at 23:19