Solved

self join or get last value from table

Posted on 2013-01-25
4
499 Views
Last Modified: 2013-01-31
I've one table.
CREATE TABLE dba.t_event(plan_event_id int,event_id int,price_code varchar(10))
--PRIMARY KEY ON ALL 3 COLUMNS COMBINED. HERE ARE THE VALUES.
INSERT INTO VALUES( 928,914,'A')
INSERT INTO VALUES( 928,914,'AC')
INSERT INTO VALUES( 928,900,'B')
So for same plan_event_id we have multiple combinations of event_id and price_code
There is a trigger on this table and it checks when we delete last plan_event_id from t_event then only delete corresponding record from other table.
So now for above table,
DELETE FROM dba.t_event WHERE plan_event_id =928, event_id = 900 AND price_code = 'B'-- DO Nothing
DELETE FROM dba.t_event WHERE plan_event_id =928, event_id = 914 AND price_code = 'AC'-- DO Nothing
DELETE FROM dba.t_event WHERE plan_event_id =928, event_id = 914 AND price_code = 'A'-- Now here the trigger should check if this is the last plan_event_id?? if yes then only
delete corresponding record from other table. Looks like I've to write self join or use MAX function. Below if what i tried
but not working
IF NOT EXISTS ( SELECT *
                          FROM deleted
                            JOIN ( SELECT MAX(plan_event_id)
                                     FROM dba.t_event
                                    WHERE deleted.plan_event_id  = dba.t_event.plan_event_id
                                     GROUP BY plan_event_id)
        BEGIN
          DELETE FROM Other Table
        END
0
Comment
Question by:cottage125
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38820310
you want to read up this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
this should help to formulate the sql, including the delete(s)
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 167 total points
ID: 38820323
rereading the question, please ignore the above.
I think it will be rather like this (as part of the delete trigger):
DELETE o
  FROM [Other Table] o
  WHERE o.plan_event_id in ( 
        SELECT d.plan_event_id
           FROM DELETED d
          WHERE NOT EXISTS( SELECT NULL FROM  dba.t_event e WHERE e.plan_event_id = d.plan_event_id )
 )

Open in new window

0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 166 total points
ID: 38820522
Here is a delete trigger against your test code that will work.
I use the EXCEPT operator to perform set logic to find the list of IDs (if any) to delete from the other table.  Since you have an primary key (index) that starts with that id it will be very efficient.
CREATE TABLE t_event(plan_event_id int,event_id int,price_code varchar(10),
--PRIMARY KEY ON ALL 3 COLUMNS COMBINED. HERE ARE THE VALUES.
CONSTRAINT PK_t_event PRIMARY KEY CLUSTERED (plan_event_id, event_id, price_code));
CREATE TABLE other (plan_event_id INT);

INSERT INTO t_event VALUES( 928,914,'A')
INSERT INTO t_event VALUES( 928,914,'AC')
INSERT INTO t_event VALUES( 928,900,'B')
INSERT INTO other VALUES ( 928 ) -- just to get this value in other table for now
GO
--So for same plan_event_id we have multiple combinations of event_id and price_code
--There is a trigger on this table and it checks when we delete last plan_event_id from t_event then only delete corresponding record from other table.
CREATE TRIGGER trg_t_event_remove_from_other_on_last_id
ON t_event
AFTER DELETE
AS
DELETE FROM other WHERE plan_event_id IN
(
SELECT plan_event_id
FROM DELETED 
EXCEPT
SELECT plan_event_id
FROM t_event)
GO


--So now for above table delete and see what happens in other table
DELETE FROM t_event WHERE plan_event_id =928 AND event_id = 900 AND price_code = 'B'-- DO Nothing
SELECT plan_event_id FROM other; -- still there
DELETE FROM t_event WHERE plan_event_id =928 AND event_id = 914 AND price_code = 'AC'-- DO Nothing
SELECT plan_event_id FROM other; -- still there
DELETE FROM t_event WHERE plan_event_id =928 AND event_id = 914 AND price_code = 'A'-- Now here the trigger should check if this is the last plan_event_id?? if yes then only 
SELECT plan_event_id FROM other; -- Gone!

--Clean up code, uncomment for testing, 
--commented out for now to avoid problems on real tables
--DROP TABLE other;  
--DROP TABLE t_event;

Open in new window

Results:
Results
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 167 total points
ID: 38820543
DELETE FROM ot
FROM dbo.OtherTable ot
INNER JOIN (
    SELECT d.plan_event_id
    FROM (
        SELECT DISTINCT plan_event_id
        FROM deleted
    ) AS d
    WHERE
        NOT EXISTS (
            SELECT 1
            FROM dbo.t_event e            
            WHERE
                e.plan_event_id = d.plan_event_id
        )
) AS plan_event_ids_removed ON
    plan_event_ids_removed.plan_event_id = ot.plan_event_id
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now