Link to home
Create AccountLog in
Avatar of bgfullerton
bgfullertonFlag for United States of America

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
Avatar of Bill Prew
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
Avatar of bgfullerton

ASKER

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

EE29136375, Sql Server - rextester


»bp
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?
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

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.
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

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
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'
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.
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
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
...
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
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'
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
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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
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'.
...
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*/
...