[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

SQL LEFT JOIN HOW DO I INSERT, DELETE AND MODIFY THE RECORDS

USE GERBER
I WANT TO BE ABLE TO UPDATE MY HISTORYAS400SCH TABLE WITHOUT CLEARING THE TABLE FIRST.

IF THE 1ST JOIN BRINGS BACK A RECORD I WANT TO DELETE IT TO THE HISTORY TABLE. IF THE SECOND JOIN BRINGS BACK RECORDS I WANT TO ADD THOSE TO THE HISTORY TABLE. IF THE 3RD OR 4TH BRINGS BACK RECORDS I WANT TO ADD THE CHANGES FROM VW_COMBO_4 TO THE HISTORY TABLE.

Declare
@_Status varchar(15)

Set @_Status = 'Deleted'
SELECT HISTORYAS400SCH.* ,@_Status as Status
FROM HISTORYAS400SCH LEFT JOIN VW_COMBO_4 ON HISTORYAS400SCH.Cut# = VW_COMBO_4.Cut#
WHERE VW_COMBO_4.Cut# IS NULL; /*DELETION OF A CUT*/

Set @_Status = 'Added'
SELECT VW_COMBO_4.* ,@_Status as Status
FROM VW_COMBO_4 LEFT JOIN HISTORYAS400SCH ON HISTORYAS400SCH.Cut# = VW_COMBO_4.Cut#
WHERE HISTORYAS400SCH.Cut# IS NULL; /* CUTS ADDED TO THE SCHEDULE*/

Set @_Status = 'Modified'
SELECT HISTORYAS400SCH.* ,@_Status as Status
FROM HISTORYAS400SCH LEFT JOIN VW_COMBO_4 ON HISTORYAS400SCH.EACHES = VW_COMBO_4.EACHES
WHERE VW_COMBO_4.EACHES IS NULL; /*EACHES CHANGED*/

SELECT VW_COMBO_4.* ,@_Status as Status
FROM VW_COMBO_4 LEFT JOIN HISTORYAS400SCH ON HISTORYAS400SCH.EACHES = VW_COMBO_4.EACHES
WHERE HISTORYAS400SCH.EACHES IS NULL; /* CASES CHANGED*/
0
PAMHART
Asked:
PAMHART
  • 2
1 Solution
 
sudheeshthegreatCommented:
hope you get the idea from this:

DELETE HISTORYAS400SCH
SELECT *
FROM HISTORYAS400SCH LEFT JOIN VW_COMBO_4 ON HISTORYAS400SCH.Cut# = VW_COMBO_4.Cut#
WHERE VW_COMBO_4.Cut# IS NULL

INSERT INTO HISTORYAS400SCH
SELECT COLUMNLIST
FROM VW_COMBO_4 LEFT JOIN HISTORYAS400SCH ON HISTORYAS400SCH.Cut# = VW_COMBO_4.Cut#
WHERE HISTORYAS400SCH.Cut# IS NULL

UPDATE HISTORYAS400SCH
SET col1 = VW_COMBO_4.col1
FROM HISTORYAS400SCH LEFT JOIN VW_COMBO_4 ON HISTORYAS400SCH.EACHES = VW_COMBO_4.EACHES
WHERE VW_COMBO_4.EACHES IS NULL
0
 
PAMHARTAuthor Commented:
SUDHEESHTHEGREAT,

THAT DELETED EVERY RECORD IN MY HISTORY TABLE.
I ONLY WANT TO DELETE THE ONES THAT DON'T MATCH.
0
 
sudheeshthegreatCommented:
sorry about the earlier one: it was considere as 2 statements: DELETE HISTORYAS400SCH and the SELECT...
because of this, all the rows were deleted after execution of first statement.

try this:
DELETE HISTORYAS400SCH
FROM HISTORYAS400SCH LEFT OUTER JOIN VW_COMBO_4 ON HISTORYAS400SCH.Cut# = VW_COMBO_4.Cut#
WHERE VW_COMBO_4.Cut# IS NULL
0
 
Brendt HessSenior DBACommented:
DELETE HISTORYAS400SCH
FROM HISTORYAS400SCH
LEFT JOIN VW_COMBO_4
    ON HISTORYAS400SCH.Cut# = VW_COMBO_4.Cut#
WHERE VW_COMBO_4.Cut# IS NULL; /*DELETION OF A CUT*/

INSERT INTO HISTORYAS400SCH
SELECT VW_COMBO_4.*
FROM VW_COMBO_4
LEFT JOIN HISTORYAS400SCH
    ON HISTORYAS400SCH.Cut# = VW_COMBO_4.Cut#
WHERE HISTORYAS400SCH.Cut# IS NULL; /* CUTS ADDED TO THE SCHEDULE*/


The third and fourth queries do not provide for making modifications as such, since all you are identifying is that the EACHES column has changed, not what record should be matched between HISTORYAS400SCH and VW_COMBO_4.  As such, you can perform a deletion (3rd query) or an insert (4th query) in HISTORYAS400SCH, but cannot update, as you do not have the data necessary to update (exception: you could simply be setting EACHES to NULL on query 3).  Since EACHES appears to be the common field you are concerned about, this query is as close to an update as I can create with this information:

UPDATE HISTORYAS400SCH
SET <Column Name in HISTORYAS400SCH> = <Equivalent column to update from in VW_COMBO_4>[, ...]
FROM HISTORYAS400SCH
INNER JOIN VW_COMBO_4
    ON HISTORYAS400SCH.EACHES = VW_COMBO_4.EACHES


0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now