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
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
SQL
Last Comment
Scott Pletcher
ASKER
That is correct.
What error / problem are you having, seemed to work in a simple test here.
EE29136375, Sql Server - rextester
»bp
EE29136375, Sql Server - rextester
»bp
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?
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
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.
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
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
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'
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'
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.
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
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
...
...
ALTER TRIGGER ZPSACTJOB__TR_INS_UPD
...
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=UPDA
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
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=UPDA
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'
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'
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
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
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'.
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*/
...
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*/
...
»bp