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:
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
zpsactjob AS Table_A
Inner Join jomast As Table_B
On Table_A.fjobno = Table_B.fjobno
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.