Databases
StackOverflow2013
Postsdbo
PODeleting records from Master- Details tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODeleting records from Master- Details tables
    primarykey
    data
    text
    <p>Following the very kind answers to my last question a short while ago I'd now like to clean up the tables from which I have just transferred records to the historical data tables of those records I'd just transferred.</p> <p>I know that DELETE FROM will delete all of the records which clearly I don't want to do so I will need to condition it with a where statement. </p> <p>What I have to date is as follows:</p> <pre><code>CREATE PROCEDURE [HistoricalData].[ClearAllLandingInformation] -- Add the parameters for the stored procedure here @cutoffdate date AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DELETE FROM Landings.LandingDetails WHERE INNER JOIN Landings.LandingHeaders ON Landings.LandingHeaders.LandingId = Landings.LandingDetails.LandingId WHERE Landings.LandingHeaders.LandingDate1 &lt;= @cutoffdate DELETE FROM Landings.LandingHeaders WHERE LandingDate1 &lt;= @cutoffdate GO </code></pre> <p>The error message when I try to execute the sql and therefore create the procedure is telling me that the syntax is wrong near the word INNER (either with or without brackets). Is it simply a case of poor syntactical construction on my part or can you not delete records from a details table based on a condition in the master table?</p> <p>Finally, working on the assumption for present that it's my sql syntax that is bad is there anything fundamentally wrong with creating a procedure that would first transfer the records from the current tables to the historical tables and then go on to delete those records that were transferred from the current table. My ultimate goal would be to produce a stored procedure that could be imported into an entity model and executed as a single function by the end user.</p> <p>Thanks</p> <p>Here is my attempt at a combined spoc to both move and then delete records that meet a certain criteria. It succeeds in deleting them from the original tables (the last part) but not in transferring them in the original part. </p> <pre><code> ALTER PROCEDURE [HistoricalData].[MoveAndClearLandingInformation] -- Add the parameters for the stored procedure here @cutoffdate date AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --First collect and move the required information INSERT INTO HistoricalData.HistoricalLandingHeaders SELECT LandingId, VesselId, TicketNumber, LandingDate1, PortOfLanding, CountryOfLanding, Logbook1, Loogbook2, Logbook3, LandingDecNumber1, LandingDecNumber2, LandingDecNumber3, DateOfPurchase, Posted, AllocatedErsId, LandingDate2, LandingDate3, VesselName, VesselOwner, VesselPLN, ModifiedDate FROM Landings.LandingHeaders WHERE Landings.LandingHeaders.LandingDate1 &lt;= @cutoffdate INSERT INTO HistoricalData.HistoricalLandingDetails SELECT LandingDetails.LandingId, ProductId, PresentationCode, PresentationState, FreshnessGrade, ProductSize, FishingArea, IcesZone, Quantity, UnitPrice, LandingDetailVatRate, SpeciesCode, Currency, ProductLandedUnderQuota, LandingDetails.ModifiedDate FROM Landings.LandingDetails INNER JOIN Landings.LandingHeaders ON Landings.LandingHeaders.LandingId = Landings.LandingDetails.LandingId WHERE Landings.LandingHeaders.LandingDate1 &lt;= @cutoffdate -- Now remove that information from the current tables DELETE FROM ld FROM Landings.LandingDetails ld JOIN Landings.LandingHeaders lh ON ld.LandingId = lh.LandingId WHERE lh.LandingDate1 &lt;= @cutoffdate DELETE FROM Landings.LandingHeaders WHERE LandingDate1 &lt;= @cutoffdate END </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDom Sinclair
    1. USDom Sinclair
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODeleting records from Master- Details tables
      1. This table or related slice is empty.
      1. VTClose
    2. VO
      singulars
      1. PODeleting records from Master- Details tables
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODeleting records from Master- Details tables
      1. This table or related slice is empty.
      1. VTDownMod
    1. CO`WHERE INNER JOIN` is clearly wrong. Should be `DELETE Landings.LandingDetails FROM Landings.LandingDetails INNER JOIN`
      singulars
      1. PODeleting records from Master- Details tables
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload