troubleshooting Question

SQL Update and refresh data from one table to another

Avatar of bgfullerton
bgfullertonFlag for United States of America asked on
SQL
20 Comments1 Solution106 ViewsLast Modified:
I have 2 SQL tables.  JOMAST and ZPSACTJOB.
JOMAST has multiple columns of data.
ZPSACTJOB has a few select columns.
I am trying to pull only those select columns from JOMAST, into ZPSACTJOB.
That part I can do using the "Insert" function. No problem there.

The problem I have is trying to update the data in ZPSACTJOB from JOMAST.
JOMAST will always have some changes to it. This table (JOMAST) is always having new data added to it.  It also has a 'Status' change.
Example: the field named "fstatus" will change from "Open" to "Released" to "Closed".
I am trying to key off the "Released" name in the "fstatus" field.
I am trying to tell the SQL statement to look at JOMAST and update the records in ZPSACTJOB where the JOMAST.fstatus field is equal to "Released".
But, at the same time, if the JOMAST.fstatus does not equal "Released" then delete the records in ZPSACTJOB.

I can use this to insert the records:

Insert INTO zpsactjob (fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus )
Select fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus from jomast
where fstatus = 'RELEASED'

If I run this statement a second time, it doubles the entries.

So I try the "Update" statement:

Update
      Table_A
Set
      Table_A.fjobno = Table_B.fjobno,
      Table_A.fpartno = Table_B.fpartno,
      Table_A.fdescript = Table_B.fdescript,
      Table_A.fquantity = Table_B.fquantity,
      Table_A.fddue_date = Table_B.fddue_date,
      Table_A.fstatus = Table_B.fstatus
From
      zpsactjob AS Table_A
      Inner Join jomast As Table_B
            On Table_A.fjobno = Table_B.fjobno
Where
      Table_B.fstatus = 'Released'

I just cannot seem to make it work


Can someone please help me find a way to make this work?
I am using SQL 2014

My ultimate goal is to have the ZPSACTJOB table update, and refresh, adding whatever new line items there are in the JOMAST table that have the "fstatus" as "Released".
And to remove any line items from the ZPSACTJOB table where the corresponding line item in the JOMAST table no longer has the "fstatus" as "Released".
The common field that ties them together is the "fjobno" field.  There will only be one entry to match to.  Because JOMAST is the 'master' table, it will never have multiple matching numbers in that field.

I have been working on this, literally, for days.
Your help is greatly appreciated.

Bob
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros