Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: stored procedure

    I am using 2 select statements to query my tables. How do I update the unitid, traffictypeid of all rows from first query by using the unitid and traffictypeid from second query. So if n rows are returned from query 1, it should transfer info from n rows from tbl 2 and del those n rows from tbl 2

    select trafficid, unitid, traffictypeid from tbltrafficschedule where (trafficlogid = 25225) and (unitid is null) and (traffictypeid is null or traffictypeid < 4)

    SELECT dbo.tblUnits.UnitID,
    CASE WHEN dbo.tblProducts.ProductName IS NULL THEN
    CASE WHEN dbo.tblAdvertisers.ShortName IS NULL
    THEN dbo.tblAdvertisers.Advertiser ELSE dbo.tblAdvertisers.ShortName END ELSE
    CASE WHEN dbo.tblAdvertisers.ShortName IS NULL
    THEN dbo.tblAdvertisers.Advertiser ELSE dbo.tblAdvertisers.ShortName END + ' - ' + dbo.tblProducts.ProductName END AS AdvProduct,
    ':' + CAST(dbo.tblUnits.UnitSize AS varchar) AS Size,
    CASE WHEN dbo.tblPrograms.ProgramSubName IS NULL
    THEN dbo.tblPrograms.ProgramName ELSE dbo.tblPrograms.ProgramName + ' - ' + dbo.tblPrograms.ProgramSubName
    END AS Program,
    CASE WHEN dbo.tblCommercialInfo.InfoID IS NOT NULL THEN dbo.tblCommercialInfo.ISCI ELSE '' END AS ISCI,
    CASE WHEN dbo.tblCommercialInfo.HouseID IS NOT NULL THEN dbo.tblCommercialInfo.HouseID ELSE '' END AS House
    FROM dbo.tblUnits INNER JOIN
    dbo.tblSchedule ON dbo.tblUnits.ScheduleID = dbo.tblSchedule.ScheduleID INNER JOIN
    dbo.tblPrograms ON dbo.tblSchedule.ProgramID = dbo.tblPrograms.ProgramID LEFT OUTER JOIN
    dbo.tblCommercialInfo ON dbo.tblUnits.InfoID = dbo.tblCommercialInfo.InfoID LEFT OUTER JOIN
    dbo.tblProducts ON dbo.tblUnits.ProductID = dbo.tblProducts.ProductID LEFT OUTER JOIN
    dbo.tblTrafficSchedule ON dbo.tblUnits.UnitID = dbo.tblTrafficSchedule.UnitID LEFT OUTER JOIN
    dbo.tblDayParts ON dbo.tblUnits.DayPartID = dbo.tblDayParts.DayPartID LEFT OUTER JOIN
    dbo.tblAdvertisers ON dbo.tblUnits.AdvertiserID = dbo.tblAdvertisers.AdvertiserID
    WHERE (dbo.tblPrograms.Network = N'SKY HIGH') AND (dbo.tblSchedule.AirDate = '10-9-2005') AND (dbo.tblTrafficSchedule.TrafficID IS NULL) AND
    (dbo.tblUnits.DealID IS NULL)
    ORDER BY dbo.tblAdvertisers.Advertiser, dbo.tblProducts.ProductName

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Is it

    Code:
    --I am using 2 select statements to query my tables. 
    
    --How do I update the unitid, traffictypeid of all rows from first query 
    --by using the unitid and traffictypeid from second query. 
    
    --So if n rows are returned from query 1, 
    --it should transfer info from n rows from tbl 2 
    --and del those n rows from tbl 2
    
    CREATE Procedure FredsJob
    
    AS
    
    DECLARE @upd_error int, @del_error int
    
    BEGIN TRAN
    
    UPDATE tbltrafficschedule TS
    SET TS.trafficid = FRED.trafficid,TS.unitid = FRED.unitid
    FROM(
    SELECT dbo.tblUnits.UnitID, 
    	CASE WHEN dbo.tblProducts.ProductName IS NULL 
    		THEN 
    		CASE WHEN dbo.tblAdvertisers.ShortName IS NULL 
    		THEN 
    			dbo.tblAdvertisers.Advertiser 
    		ELSE dbo.tblAdvertisers.ShortName 
    		END 
    	ELSE 
    		CASE WHEN dbo.tblAdvertisers.ShortName IS NULL 
    		THEN 
    			dbo.tblAdvertisers.Advertiser 
    		ELSE 
    			dbo.tblAdvertisers.ShortName 
    		END + ' - ' + dbo.tblProducts.ProductName 
    	END 
    	AS AdvProduct,
    	 ':' + CAST(dbo.tblUnits.UnitSize AS varchar) AS Size, 
    	CASE WHEN dbo.tblPrograms.ProgramSubName IS NULL 
    	THEN 
    		dbo.tblPrograms.ProgramName 
    	ELSE dbo.tblPrograms.ProgramName + ' - ' + dbo.tblPrograms.ProgramSubName 
    	END
    	AS Program, 
    	CASE WHEN dbo.tblCommercialInfo.InfoID IS NOT NULL 
    	THEN
    		dbo.tblCommercialInfo.ISCI 
    	ELSE
    		'' 
    	END 
    	AS ISCI, 
    	CASE WHEN dbo.tblCommercialInfo.HouseID IS NOT NULL 
    	THEN 
    		dbo.tblCommercialInfo.HouseID 
    	ELSE '' 
    	END 
    	AS House
    FROM dbo.tblUnits
    INNER JOIN dbo.tblSchedule ON dbo.tblUnits.ScheduleID = dbo.tblSchedule.ScheduleID 
    INNER JOIN dbo.tblPrograms ON dbo.tblSchedule.ProgramID = dbo.tblPrograms.ProgramID 
    LEFT OUTER JOIN dbo.tblCommercialInfo ON dbo.tblUnits.InfoID = dbo.tblCommercialInfo.InfoID 
    LEFT OUTER JOIN dbo.tblProducts ON dbo.tblUnits.ProductID = dbo.tblProducts.ProductID 
    LEFT OUTER JOIN dbo.tblTrafficSchedule ON dbo.tblUnits.UnitID = dbo.tblTrafficSchedule.UnitID 
    LEFT OUTER JOIN dbo.tblDayParts ON dbo.tblUnits.DayPartID = dbo.tblDayParts.DayPartID 
    LEFT OUTER JOIN dbo.tblAdvertisers ON dbo.tblUnits.AdvertiserID = dbo.tblAdvertisers.AdvertiserID
    WHERE (dbo.tblPrograms.Network = N'SKY HIGH') 
    	AND (dbo.tblSchedule.AirDate = '10-9-2005') 
    	AND (dbo.tblTrafficSchedule.TrafficID IS NULL) 
    	AND (dbo.tblUnits.DealID IS NULL)
    ) AS Fred
    
    WHERE Fred.unitid = TS.unitid
    AND Fred.traffictypeid = TS.traffictypeid
    AND (TS.trafficlogid = 25225) 
    AND (TS.unitid is NULL) 
    AND (TS.traffictypeid is NULL 
    	OR TS.traffictypeid < 4) 
    
    SELECT @upd_error = @@ERROR
    
    DELETE tbltrafficschedule FROM tbltrafficschedule 
    INNER JOIN dbo.tblSchedule ON dbo.tblUnits.ScheduleID = dbo.tblSchedule.ScheduleID 
    INNER JOIN dbo.tblPrograms ON dbo.tblSchedule.ProgramID = dbo.tblPrograms.ProgramID 
    LEFT OUTER JOIN dbo.tblCommercialInfo ON dbo.tblUnits.InfoID = dbo.tblCommercialInfo.InfoID 
    LEFT OUTER JOIN dbo.tblProducts ON dbo.tblUnits.ProductID = dbo.tblProducts.ProductID 
    LEFT OUTER JOIN dbo.tblTrafficSchedule ON dbo.tblUnits.UnitID = dbo.tblTrafficSchedule.UnitID 
    LEFT OUTER JOIN dbo.tblDayParts ON dbo.tblUnits.DayPartID = dbo.tblDayParts.DayPartID 
    LEFT OUTER JOIN dbo.tblAdvertisers ON dbo.tblUnits.AdvertiserID = dbo.tblAdvertisers.AdvertiserID
    WHERE (dbo.tblPrograms.Network = N'SKY HIGH') 
    	AND (dbo.tblSchedule.AirDate = '10-9-2005') 
    	AND (dbo.tblTrafficSchedule.TrafficID IS NULL) 
    	AND (dbo.tblUnits.DealID IS NULL)
    	AND (dbo.tblTrafficSchedule.trafficlogid = 25225) 
    	AND (dbo.tblTrafficSchedule.unitid is NULL) 
    	AND (dbo.tblTrafficSchedule.traffictypeid is NULL 
    		OR dbo.tblTrafficSchedule.traffictypeid < 4) 
    
    SELECT @del_error = @@ERROR
    
    IF @upd_error = 0 AND @del_error = 0
       PRINT "The Operation Completed succesfuly"    
       COMMIT TRAN
    END
    ELSE
    BEGIN
       IF @upd_error <> 0 
          PRINT "An error occurred during execution of the UPDATE 
          statement." 
    
       IF @del_error <> 0
          PRINT "An error occurred during execution of the DELETE 
          statement." 
       ROLLBACK TRAN
    END
    GW
    Last edited by GWilliy; 10-03-05 at 22:55.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •