asked on

SQL Update and refresh data from one table to another

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

Bill Prew

Is this a one to one relationship, meaning there will only be one row in each of those two tables for a given fjobno value?

»bp

bgfullerton

ASKER

That is correct.

Bill Prew

What error / problem are you having, seemed to work in a simple test here.

EE29136375, Sql Server - rextester

»bp

bgfullerton

ASKER

I don't actually "get an error", it just does not update.
One thing I noticed on your "test" is the fjobno field is set up as an "integer".
In my database, the JOMAST table has an "Identity_Column" it uses as the Primary key, and I did not have a field in the ZPSACTJOB table as a "Integer" field. The fjobno field in both tables is a varchar format. I was not going to use the "Identity_Column" from the ZPSACTJOB table, so I did not copy that field over. Do you think that may be my problem? Should I copy the "Identity_Column" over anyway, even though I am not going to use it?

Scott Pletcher

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER ZPSACTJOB__TR_INS_UPD
ON dbo.ZPSACTJOB
AFTER DELETE, INSERT, UPDATE
AS
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON; 
SET NUMERIC_ROUNDABORT OFF;
SET XACT_ABORT ON;

DECLARE @action char(1) /*D=DELETE,I=INSERT,U=UPDATE*/

IF EXISTS(SELECT 1 FROM deleted)
    IF EXISTS(SELECT 1 FROM inserted)
        SET @action = 'U'
    ELSE
        SET @action = 'D'
ELSE
    SET @action = 'I'

IF @action = 'D'
    DELETE FROM JM
    FROM inserted ZP_NEW 
    INNER JOIN dbo.JOMAST JM ON 
        JM.fjobno = ZP_NEW.fjobno
    WHERE ZP_NEW.fstatus = 'Released'
ELSE
IF @action = 'I'
    INSERT INTO dbo.JOMAST ( fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus )
    SELECT fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus
    FROM inserted
    WHERE status = 'Released'
ELSE
IF @action = 'U'
    DELETE FROM JM
    FROM inserted ZP_NEW 
    INNER JOIN deleted ZP_OLD ON
        ZP_NEW.fjobno = JM.fjobno AND
        ZP_NEW.fstatus = 'Released'
    INNER JOIN dbo.JOMAST JM ON 
        JM.fjobno = ZP_NEW.fjobno
    WHERE ZP_NEW.fstatus <> 'Released'
/*end of trigger*/
GO

Open in new window

bgfullerton

ASKER

Scott,
Just looking this over and I have a question.
I see a "Delete From JM" and "INSERT INTO dboJOMAST"
These are not going to do anything to the live table of JOMAST are they?
That table is the live data and I did not want anything to happen to that table.
Only to "pull" data into the new ZPSACTJOB table.
That was the reason why I am working from this ZPSACTJOB table as I did not want anything to mess with the live data.

Sorry if this may sound like a silly question.
I am not real good at the SQL stuff.
I am practicing on a "test system" so I do not mess anything up with the live one.

Scott Pletcher

Nah, not silly, I got the tables backwards, so basically I just have to reverse the two tables in the code:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER JOMAST__TR_INS_UPD
ON dbo.JOMAST
AFTER DELETE, INSERT, UPDATE
AS
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON; 
SET NUMERIC_ROUNDABORT OFF;
SET XACT_ABORT ON;

DECLARE @action char(1) /*D=DELETE,I=INSERT,U=UPDATE*/

IF EXISTS(SELECT 1 FROM deleted)
    IF EXISTS(SELECT 1 FROM inserted)
        SET @action = 'U'
    ELSE
        SET @action = 'D'
ELSE
    SET @action = 'I'

IF @action = 'D'
    DELETE FROM ZP
    FROM inserted JM_NEW
    INNER JOIN dbo.ZPSACTJOB ZP ON 
        ZP.fjobno = JM_NEW.fjobno
    WHERE JM_NEW.fstatus = 'Released'
ELSE
IF @action = 'I'
    INSERT INTO dbo.ZPSACTJOB ( fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus )
    SELECT fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus
    FROM inserted
    WHERE status = 'Released'
ELSE
IF @action = 'U'
    DELETE FROM ZP
    FROM inserted JM_NEW
    INNER JOIN deleted JM_OLD ON
        JM_NEW.fjobno = ZP.fjobno AND
        JM_NEW.fstatus = 'Released'
    INNER JOIN dbo.ZPSACTJOB ZP ON 
        ZP.fjobno = JM_NEW.fjobno
    WHERE JM_NEW.fstatus <> 'Released'
/*end of trigger*/
GO

Open in new window

bgfullerton

ASKER

I made 1 correction.
After the "If @Action = 'I'
in the "Where" statement, it needed the "f" in fstatus.

Thank you so much for what you did.
There seems to be some small issue yet in the "U" Action section.
the "ZP.fjobno" has a red line under it.
I ran it and got an error.
Because what you did here is a 'little' over my head, I thought I would ask if you see what the issue is, before I try to decipher it.
I attached a screen shot.
Capture.JPG

Scott Pletcher

It's just that the Joins are out of order now that I've swapped the tables. This will fix it:

IF @action = 'U'
DELETE FROM ZP
FROM inserted JM_NEW
INNER JOIN dbo.ZPSACTJOB ZP ON
ZP.fjobno = JM_NEW.fjobno
INNER JOIN deleted JM_OLD ON
JM_NEW.fjobno = ZP.fjobno AND
JM_NEW.fstatus = 'Released'
WHERE JM_NEW.fstatus <> 'Released'

bgfullerton

ASKER

I made the changes.
Ran the statement and it ran fine. Said it was "Completed Successfully"

I went into the ERP and made a change to the Status of a Job. From "Released" to Closed.
I reran the statement.
I got this error.
Does it mean anything to you?

Msg 2714, Level 16, State 2, Procedure JOMAST__TR_INS_UPD, Line 104
There is already an object named 'JOMAST__TR_INS_UPD' in the database.

Bill Prew

Works fine here with character job numbers, assuming you have matches occurring there, you might want to start with a SELECT and make sure you're seeing the joined rows you expect.

EE29136375, Sql Server - rextester

»bp

Scott Pletcher

Yes, there's already a trigger with that name. I can't imagine anyone there would have named a trigger that way, so it must be an earlier version of my trigger. So you just have to change the "CREATE" to "ALTER":

...
ALTER TRIGGER ZPSACTJOB__TR_INS_UPD
...

bgfullerton

ASKER

Another day. You have no idea how much I appreciate your help with this. I am an "IT" guy, not a "Code" guy. So while I do 'kind of' understand what is happening, it is not my field of experience.

Something is still not right, and I do not know what. I try to go thru each line and when I "hoover" over the word, it tells me the function and table name that word relates to. I do not see why this is not working. (frustrating)
Here is the statement I have, as it is right now. I have not changed anything but the "Alter Trigger ZPSACTJOB" part.
When I run it, it says it ran successfully.
But when I run a "Select" on JOMAST and ZPSACTJOB, the results show JOMAST.fstatus as "Completed" and the ZPSACTJOB.fstatus as "Released". I would have expected the ZPSACTJOB table to return no results, because it should have deleted the line due to the fact that the JOMAST.fstatus was not "Released", it was now something else.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
Alter TRIGGER ZPSACTJOB_TR_INS_UPD
ON dbo.JOMAST
AFTER DELETE, INSERT, UPDATE
AS
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON;
SET NUMERIC_ROUNDABORT OFF;
SET XACT_ABORT ON;

DECLARE @action char(1) /*D=DELETE,I=INSERT,U=UPDATE*/

IF EXISTS(SELECT 1 FROM deleted)
IF EXISTS(SELECT 1 FROM inserted)
SET @action = 'U'
ELSE
SET @action = 'D'
ELSE
SET @action = 'I'

IF @action = 'D'
DELETE FROM ZP
FROM inserted JM_NEW
INNER JOIN dbo.ZPSACTJOB ZP ON
ZP.fjobno = JM_NEW.fjobno
WHERE JM_NEW.fstatus = 'Released'
ELSE
IF @action = 'I'
INSERT INTO dbo.ZPSACTJOB ( fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus )
SELECT fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus
FROM inserted
WHERE fstatus = 'Released'
ELSE
IF @action = 'U'
DELETE FROM ZP
FROM inserted JM_NEW
INNER JOIN dbo.ZPSACTJOB ZP ON
ZP.fjobno = JM_NEW.fjobno
INNER JOIN deleted JM_OLD ON
JM_NEW.fjobno = ZP.fjobno AND
JM_NEW.fstatus = 'Released'

WHERE JM_NEW.fstatus <> 'Released'
/*end of trigger*/
GO

Scott Pletcher

IF @action = 'U'
DELETE FROM ZP
FROM inserted JM_NEW
INNER JOIN dbo.ZPSACTJOB ZP ON
ZP.fjobno = JM_NEW.fjobno
INNER JOIN deleted JM_OLD ON
JM_OLD.fjobno = ZP.fjobno AND
JM_OLD.fstatus = 'Released'
WHERE FM_NEW.fstatus <> 'Released'

bgfullerton

ASKER

Is that last line really supposed to be "Where FM_NEW.fstatus<>'Released'

The "FM" does not match up with anything that I can see. I changed it to "JM", and it says the run was successful, but the line item is still in the ZPACTJOB table.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bgfullerton

ASKER

You are my hero.
That was it. I had taken a job and changed it to something other than "Released", before we actually had things working.
I did not know that the script needed to "see it as Released" before, and then "see it as something other than Released" after running again.

I changed it to Released, ran the script, changed it to something else and ran the script again.
Presto, it was gone.

Thank you so much for your help.

bgfullerton

ASKER

You were very patient and understanding with someone who did not know a lot about writing the SQL statement to make things work.
You time is really appreciated

Scott Pletcher

You're welcome!

I had the restriction of going from 'Released' to <> 'Released' in order to delete because only rows that were 'Released' should be in the other table, so they would only need deleted if they changed from 'Released' to <> 'Released'.

Scott Pletcher

...
IF @action = 'U'
BEGIN
INSERT INTO dbo.ZPSACTJOB ( fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus )
SELECT fjobno, fpartno, fdescript, fquantity, fddue_date, fstatus
FROM inserted JM_NEW
INNER JOIN deleted JM_OLD ON
JM_OLD.fjobno = JM_NEW.fjobno AND
JM_OLD.fstatus <> 'Released'
WHERE FM_NEW.fstatus = 'Released'
DELETE FROM ZP
FROM inserted JM_NEW
INNER JOIN deleted JM_OLD ON
JM_OLD.fjobno = JM_NEW.fjobno AND
JM_OLD.fstatus = 'Released'
INNER JOIN dbo.ZPSACTJOB ZP ON
ZP.fjobno = JM_NEW.fjobno
WHERE FM_NEW.fstatus <> 'Released'
END /*IF*/
...