Multiple Record Updates In One Statement
Apr 15, 2006
Is there a way to update all of the records in a table all at once using the results of a select of a different table's data?
For example.
There are two tables, each have a primary index of catnum. One table (invoice_items) contains the line items (catnum) of customer invoices. The other table (sales_history) is a sales history table. I want to select all data from the invoice_items and sum the sales for various time periods (sales_0to30days, sales_31to60days, etc.) I then want to update the sales_history table which has columns: catnum, sales_0to30, sales_31to60, etc.). I want to run this daily to update all records.
Any help would be appreciated. Many thanks.
View 3 Replies
ADVERTISEMENT
Oct 30, 2007
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
View 5 Replies
View Related
Jul 23, 2005
Approximately once a month, a client of ours sends us a bunch ofcomma-delimited text files which I have to clean up and then importinto their MS SQL database. All last week, I was using a Cold Fusionscript to upload the cleaned up files and then import the records theycontained into the database, though obviously, the process tookfriggin' forever, and could have been done 500x quicker had I done itdirectly on the server. My SQL knowledge is somewhat limited, however,so I had no choice but to stick to what I know, which is Cold Fusionprogramming.In the process of cleaning up some of these comma-delimited text files,I inadvertently messed up some of the 10-digit zip codes, by applyingthe wrong Excel formula to the ZIP columns. These records were importedinto the database with obviously incorrect zip codes (ie: singledigit). So now, I have to find the best and quickest way possible tocompare these records in the database (that have the single digit zipcodes) with the unmodified data, and to update the zip codes with thecorrect data.I've had no luck setting up a TEXT file as an ODBC datasource, -- soI've ruled that out completely. I've also managed to import theunmodified data into an Access database, and to set it up as a ColdFusion datasource. But it seems this 2nd road I've been traveling downis not the ideal approach either.My question is, -- assuming that I'll be able to import the recordsfrom the Access database into their own table on the SQL server, -- howshould I go about the process of updating these records that have theincorrect zip codes?Here is the specific logic I would need to employ:* Here is a list of records, each of which contains an incorrect1-digit zip code (Database A / Table A)* Here is a much longer list of records (which contains all of therecords from Database A / Table A + thousands more), each of whichcontains a correct 5-digit zip code (Database B / Table B)* Compare both lists of records and run the following query/update:When a record in Database A / Table A has matching "name", "address1",and "address2" values as a record in Database B / Table B -- update therecord in Database B / Table B with the zip code from the matchingrecord in Database A / Table A.Would anyone care to write a sample query for me that I could rundirectly on the SQL server, or at least give me some pointers?The specific field names are as follows:name,address1,address2,city,state,zipThanks in advance!- yvan
View 5 Replies
View Related
Mar 9, 2015
I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.
View 9 Replies
View Related
Aug 16, 2015
How to do some procedural updates based on a record string. What I wish to do.
If the value (of type string) in my column contains the ‘/’ character I wish to ‘do something’
If the value (of my string) in my column contains the ‘-‘ character I wish to ‘do something else’.
Assume my column contains both ‘/’ and ‘-‘ in the same table.column, but not within the same record.
The data in question is in
tbl_Quotes.tLastDateValue
I’ve found I can use something like this to search my string.
SELECT
Count(CHARINDEX('/',tbl_Quotes.tLastDateValue))
FROM tbl_Quotes
WHERE
CHARINDEX('/',tbl_Quotes.tLastDateValue)>0
And similar for ‘-‘ records.
I’m thinking something like this could be used as the test.
How do I put this into play, I’m thinking of an IF or CASE but not sure how to best test and which program flow to use in SQL.
View 6 Replies
View Related
May 25, 2004
Hello,
I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?
Thanks you
Jeff
View 2 Replies
View Related
Mar 9, 2002
Is it possible to do an update on multiple records. I have fields CusOrderID and CusCreditAmt in table CusOrder; and fields CusOrderID and CreditAmt in table CusCredits. I would like to update the value in the CusCreditAmt field in CusOrder to equal the CreditAmt field in CusCredits where CusOrderID is the same in both tables.
I tried doing this in a stored procedure based on a View with an inner join between the 2 tables as follows:
Update View1
Set CusCreditAmt = CreditAmt
I received an error that the subquery returned more than one value. Is there anyway to do something like this and make it work?
Many thanks for any advice.
View 1 Replies
View Related
Jul 23, 2014
Writing this code using a CTE or any other method which do not use a table variable or a temp table as the users do not permissions to run this code from the application because of their roles which are required for creating and dropping the temp tables. I replaced table variable in place of Temp tables however the query never completes, probably because of the number of rows.
SELECT A.[Entry No_], A.[G_L Account No_],A.[Gen_ Prod_ Posting Group],A.[Document No_],A.[Posting Date]
INTO temp1
FROM [G_L Entry] as A ,[G_L Account] as B
where A.[G_L Account No_]= B.[No_]
SELECT VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group] , GLILER.[G_L Entry No_]
INTO temp2
[code]....
View 4 Replies
View Related
May 25, 2004
I am a total newbie to SQL Server, but have a good idea about how things operate..
I have a "job" that runs multiple (10) update queries for me in different tables.
Can I create a stored procedure to do these so that I can make one call to them?
If not, how can I call the job to start external to MS SQL?
View 1 Replies
View Related
Jul 20, 2005
Using SQL2000, is there another way of optimizing the original query below.TIA,BobUpdate Transactionset field1 = (select (sum(tax)-sum(credit))/sum(credit) from tblOrder wheretblOrder.id = Transaction.id),field2 = (select avg(price) from tblOrder where tblOrder.id =Transaction.id),field3 = (select min(cost) from tblOrder where tblOrder.id = Transaction.id)etc..(there are six additional updates)Is this a quicker way:Update TransactionSET field1 = (sum(tax)-sum(credit))/sum(credit) , field2 = avg(price) ,field3 = min(cost) , etc.....FROM tblOrderWHERE tblOrder.id = Transaction.id
View 1 Replies
View Related
Jul 20, 2005
Howdy,I need to write an update query with multiple aggregate functions.Here is an example:UPDATE tSETt.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ),t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y )FROM dbo.test tBasically I need to get some aggregate statistics about the rows offoo and store them in rows of t. The above statement works fine...butnote how the two subSelect's have the exact same WHERE clause. Thisscreams at me to combine them...but how? I would like to havesomething like this in my query:SELECT avg(f.q), sum(f.q) FROM dbo.foo f WHERE f.p = 2...and somehow store the results in t.a and t.b. Is there any way todo this?Thanks before hand!
View 6 Replies
View Related
Sep 27, 2006
I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What isare the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.
TIA,
Barkingdog
View 4 Replies
View Related
Aug 28, 2007
I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.
Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?
View 9 Replies
View Related
Mar 13, 2002
I have triggers in place on a table that do various checks on data input. It is clear that because of these triggers I cannot do updates on multiple records in this table. When I do, I receive an error that "subquery returned more than one value." Is there anyway to work around this by temporarily turning off triggers or something else?
Many thanks for advice.
View 2 Replies
View Related
Apr 21, 2004
Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.
UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20
Error I am getting is :
Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
What is the best possible way to make it work? Thank you.
View 7 Replies
View Related
Dec 28, 2007
I was able to catch one update but not multiple updates or batch updates done to the table. I know the updated records are residing in inserted and deleted tables. Without using cursors, how can i read and compare all the rows in these two tables?
Following is the table structure:
Customer_Master(custmastercode, customer_company_name,updated_by)
Following is the trigger:
ALTER TRIGGER [TR_UPDATE_CUST]
ON [dbo].[CUSTOMER_MASTER]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM inserted)
BEGIN
declare @custcode int
Declare @message varchar(5000)
Declare @custommessage varchar(2000)
Declare @CUSTMASTERCODE int
Declare @CUSTOMER_COMPANY_NAME varchar(50)
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
select @custcode = [CUSTMASTERCODE],@UPDATED_BY = [UPDATED_BY] from inserted
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
IF(update([CUSTOMER_COMPANY_NAME]))
Begin
select @UCUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from deleted
select @CUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from inserted
Set @custommessage = 'Customer company name changed from ' + @UCUSTOMER_COMPANY_NAME + ' to ' + @CUSTOMER_COMPANY_NAME + '.'
Set @message = @message + @custommessage
End
Set @message = @message + ' Updated by ' + @UPDATED_BY + ' at ' + CAST(getdate() AS VARCHAR(20))+ '.'
INSERT INTO [CHANGE_HISTORY]
([CUSTMASTERCODE]
,[CHANGE_DETAILS])
VALUES (@custcode, @message)
END
END
View 7 Replies
View Related
Jan 10, 2008
Hi,
How can we insert multiple records in a OLEDB destination table for each entry from the source table.
To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record.
how is this achieved.
thanks.
View 4 Replies
View Related
Nov 8, 2006
Hello,
I have a stored procedure that creates a temporary table, and then populates it using an INSERT and then a series of UPDATE statements. The procedure then returns the temporary table which will contain data in all of its columns.
When I call this procedure from SSRS 2005, the rowset returned contains data in only those columns that are populated by the INSERT statement. The columns that are set via the UPDATE statements are all empty. I read (in the Hitchhikers Guide to Reporting Services) that SSRS will only process the first rowset in a stored procedure that generates multiple rowsets. Is this true? Is this why SSRS does not retrieve data for the columns that are populated by the UPDATE statements?
Here is the stored procedure:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- File: sp_GetProgramsWatchedByDateRange.sql
-- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse.
-- Note that some of that data used by this procedure are obtained from the RMS_EPG database
-- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file.
-- Auth: H Hunsaker
-- Date: 11/07/2006
-- Example invocation
-- EXEC dbo.sp_GetProgramsWatchedByDateRange ...
-- Arguments/Parameters:
-- Parameter Name Type Description
-- 3. StartDate datetime First date of reporting period
-- 4. EndDate datetime Last date of reporting period
-- TerseMode bit Return all columns? (1 = no, 0 = yes)
-- 5. AsXML bit Resultset format (0 = standard, 1 = XML)
-- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled
IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL
DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
GO
CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@TerseMode bit = 0,
@AsXML bit = 0,
@Debug bit = 0
AS
-- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse.
-- So I am going to the RMS_EPG database to obtain that information.
-- We will have to ensure that the 2 databases are generated at the same or a matching time
-- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.
-- Debug code for testing
-- DECLARE @StartDate datetime
-- DECLARE @EndDate datetime
-- DECLARE @TerseMode bit
--SET @StartDate = NULL
--SET @EndDate = NULL
--SET @TerseMode = 1
SET NOCOUNT ON
CREATE TABLE #programWatched
(
--IPTV device ID
tdeviceId uniqueidentifier NULL,
taccountId uniqueidentifier NULL,
-- Basic program information
tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db.
tprogramId uniqueidentifier NULL, -- programID generated by IPTV
tprogramTitle varchar(150) NULL,
tprogramEpisodeTitle varchar(100) NULL,
tprogramDescription varchar(500) NULL,
toriginDateTime datetime NULL,
tduration bigint NULL,
tprogramType nvarchar(100) NULL,
tchannelCallName nvarchar(20) NULL,
--Rating
programMPAARating varchar(50) NULL,
programVCHIPRating varchar(50) NULL,
programMPAARatingVal smallint NULL,
programVChipRatingVal smallint NULL,
-- Categories
programGenre varchar(50) NULL,
programCategory1 varchar(50) NULL,
programCategory2 varchar(50) NULL,
programCategory3 varchar(50) NULL,
programCategory4 varchar(50) NULL,
-- Roles
programActor1FirstName varchar(50) NULL,
programActor1LastName varchar(50) NULL,
programActor1 varchar(100) NULL,
programActor2FirstName varchar(50) NULL,
programActor2LastName varchar(50) NULL,
programActor2 varchar(100) NULL,
programActor3FirstName varchar(50) NULL,
programActor3LastName varchar(50) NULL,
programActor3 varchar(100) NULL,
programActor4FirstName varchar(50) NULL,
programActor4LastName varchar(50) NULL,
programActor4 varchar(100) NULL,
programActor5FirstName varchar(50) NULL,
programActor5LastName varchar(50) NULL,
programActor5 varchar(100) NULL,
programActor6FirstName varchar(50) NULL,
programActor6LastName varchar(50) NULL,
programActor6 varchar(100) NULL,
programActor7FirstName varchar(50) NULL,
programActor7LastName varchar(50) NULL,
programActor7 varchar(100) NULL,
programActor8FirstName varchar(50) NULL,
programActor8LastName varchar(50) NULL,
programActor8 varchar(100) NULL,
programDirectorFirstName varchar(50) NULL,
programDirectorLastName varchar(50) NULL,
programDirector varchar(100) NULL,
programWriterFirstName varchar(50) NULL,
programWriterLastName varchar(50) NULL,
programWriter varchar(100) NULL,
programProducerFirstName varchar(50) NULL,
programProducerLastName varchar(50) NULL,
programProducer varchar(100) NULL,
-- Flags
ClosedCaption bit NULL,
InStereo bit NULL,
Repeats bit NULL,
New bit NULL,
Live bit NULL,
Taped bit NULL,
Subtitled bit NULL,
SAP bit NULL,
ThreeD bit NULL,
Letterbox bit NULL,
HDTV bit NULL,
Dolby bit NULL,
DVS bit NULL,
FlagOrdinalValue smallint NULL,
-- Channel
tchannelId int NULL,
callLetters varchar(20) NULL,
displayName varchar(50) NULL,
type varchar(50) NULL,
networkAffiliation varchar(50) NULL
)
-- I store the program watching data in a temp table because
-- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid.
-- Use of a temp table with a series of updates allow me more control over the result set.
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,
ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
AND originTime BETWEEN @StartDate AND @EndDate
ELSE
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,
ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
-- AccountId/SubscriberId
UPDATE #programWatched
SET taccountId = (SELECT accountId
FROM DW_BRDB_bm_device d
WHERE d.deviceId = tdeviceId)
-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId)
-- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs
UPDATE #programWatched
SET tchannelCallName = (SELECT TOP 1 channelCallName
FROM DW_EPG EPG
WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))
UPDATE #programWatched
SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)
-- Rating (otained from programValues, can also be obtained from programFlags)
UPDATE #programWatched
SET programMPAARating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)
UPDATE #programWatched
SET programMPAARatingVal = CASE programMPAARating
WHEN 'G' THEN 10
WHEN 'PG' THEN 25
WHEN 'PG-13' THEN 30
WHEN 'R' THEN 35
WHEN 'NC-17' THEN 50
WHEN 'NRAO' THEN 60
WHEN 'NR' THEN 0
ELSE 0
END
UPDATE #programWatched
SET programVChipRating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)
UPDATE #programWatched
SET programVChipRatingVal = CASE programVChipRating
WHEN 'TV-Y' THEN 10
WHEN 'TV-Y7' THEN 20
WHEN 'TV-G' THEN 35
WHEN 'TV-PG' THEN 40
WHEN 'TV-14' THEN 45
WHEN 'TV-MA' THEN 60
ELSE 0
END
-- Genre
UPDATE #programWatched
SET programGenre = (SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId
INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId
WHERE tprogram = pc.programID)
-- Categories
UPDATE #programWatched
SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID)
UPDATE #programWatched
SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))
UPDATE #programWatched
SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))
UPDATE #programWatched
SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))
-- Roles
UPDATE #programWatched
SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched
SET programDirectorLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched
SET programDirector = programDirectorLastName + ' , ' + programDirectorFirstName
UPDATE #programWatched
SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched
SET programWriterLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched
SET programWriter = programWriterLastName + ' , ' + programWriterFirstName
UPDATE #programWatched
SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched
SET programProducerLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched
SET programProducer = programProducerLastName + ' , ' + programProducerFirstName
UPDATE #programWatched
SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched
SET programActor1LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched
SET programActor1 = programActor1LastName + ' , ' + programActor1FirstName
UPDATE #programWatched
SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched
SET programActor2LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched
SET programActor2 = programActor2LastName + ' , ' + programActor2FirstName
UPDATE #programWatched
SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched
SET programActor3LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched
SET programActor3 = programActor3LastName + ' , ' + programActor3FirstName
UPDATE #programWatched
SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched
SET programActor4LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched
SET programActor4 = programActor4LastName + ' , ' + programActor4FirstName
UPDATE #programWatched
SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched
SET programActor5LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched
SET programActor5 = programActor5LastName + ' , ' + programActor5FirstName
UPDATE #programWatched
SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched
SET programActor6LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched
SET programActor6 = programActor6LastName + ' , ' + programActor6FirstName
UPDATE #programWatched
SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched
SET programActor7LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched
SET programActor7 = programActor7LastName + ' , ' + programActor7FirstName
UPDATE #programWatched
SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched
SET programActor8LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched
SET programActor8 = programActor8LastName + ' , ' + programActor8FirstName
-- Channel (provider) Call Letters, Display Name and Type
-- Is this correct? Should we get the channelId from the schedule table?
-- Is this efficient? View execution plan
UPDATE #programWatched
SET tchannelId = (SELECT TOP 1 c.channelId
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram)
UPDATE #programWatched
SET callLetters = (SELECT TOP 1 c.channelCallLetters
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched
SET displayName = (SELECT TOP 1 c.channelDisplayName
FROM RMS_EPG..channel c
JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched
SET type = (SELECT TOP 1 c.channelType
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched
SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
IF @TerseMode = 0
SELECT *
FROM #programWatched
ORDER BY toriginDateTime
ELSE
-- Get only Genre, title, show date/time, rating, call letters
SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName
FROM #programWatched
ORDER BY toriginDateTime
DROP TABLE #programWatched
SET NOCOUNT OFF
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I also tried a query that populates some of its columns via subqueries. The query works fine when executed by the SQL Sevrer Query Analyzer,
meaning that all columns contain values, but when executed from SSRS, the columns that are poulated by the subqueries are empty, and only the columns that are not set by subqueries contain values:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT PW.DeviceID,
PW.originTime AS 'When Watched',
PW.programID,
PW.Duration AS 'Duration Seconds',
(SELECT TOP 1 programTitle FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime
AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) AS Title,
(SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND
DATEADD(second, durationSecs, DW_EPG.scheduleTime)) As program,
(SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN
DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Genre,
(SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime
BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Category
FROM DW_EventClientProgramWatched PW
ORDER BY DeviceId, programId, originTime
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Any help is appreciated
View 3 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jun 11, 2008
SOURCE TABLE
ID SEQ COM VARCHAR(10)
1 1 I
1 2 am
1 3 doctor
2 1 I
2 2 am nurse
DESTINATION TABLE --Each comm field is varchar(20)
ID_COM1___COM2______COM3
1__I______am_________doctor
2__I______am nurse____NULL
Please Help
View 6 Replies
View Related
Apr 20, 2015
have this update statement that works for one record. How do I write it to include multiple records at once. see sample below.
update
mklopt
set
FRMDAT = '12/31/2014'
where
JOBCOD = 'PH14789'
I also want to include the following instead of running it one at a time
PH17523
PH17524
PH17525
PH17553
PH17555
PH17556
PH17557
PH17558
PH17571
PH17573
PH17574
PH17575
PH17576
PH17577
PH1757
View 5 Replies
View Related
Oct 12, 2007
I have a table with member information. Each member has multiple effective dates. I would like to query to find the record for each member that has the maximum effective date. A sample table is below:
MEMBERID EFECTIVEDATE
------------ ------------------
699361401 20070101
699361401 20070501
732612701 20070101
732612701 20070501
575424301 20070101
575424301 20070501
192939801 20070101
192939801 20070501
458645001 20070101
458645001 20070501
View 4 Replies
View Related
Jan 19, 2007
I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.
View 4 Replies
View Related
Dec 19, 2007
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
View 4 Replies
View Related
Oct 13, 2007
I have two table and there are releationsheep between them.
Topic:
topicId, topicName, topicDate
Reply:
replyId,topicId,replyName,replyDate
I want to insert two table at the same time.
Topic: 1,example,10/21/2007
Reply:1,1,example, 10/21/2007
View 1 Replies
View Related
Dec 9, 2002
I am trying to do an update to a database with the code below, the code will work in generating a single record (if i exclude the IF statement) but i need it to create multiple new records based on the IF statement (or something similar) i have in the code. It needs to create updates for every record that has a parentguid that matches the one specified in the code.
the idea is, the hierachy structure in the tables are:
Location1
--> Part1
--> part2 etc
Location2
--> part1 etc
i want it so that when i input a new part it updates to all the locations. The locations all have the same parentguid (the one specified in the code)
With regards to the stored procedure it executes, this stored procedure creates the entry. I don't really want to change the stored procedure because other functions reference and use it.
My code:
DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID2 VARCHAR(50)
select @parentguid2 = parentguid from dsdba.itemgroups
if @PARENTGUID2 = '8CF850AD-2026-411B-AABE-BF1584624EB3'
BEGIN
SET @PREFIX = 'JUST'
SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS WHERE CODE = @PREFIX
SET @CODE = @PREFIX + '02'
SELECT @GUID = NEWID()
EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, 'JMB', @PARENTGUID, 1
END
:confused:
View 1 Replies
View Related
Mar 3, 2004
can anyone help to explain how to insert multiple record into one/two table?
ex:lets say when user specify start date and end date, then we need to created and insert the record on that duration.
and how to do with insert the record weekly or monthly?until the end of the date!
View 1 Replies
View Related
Jul 19, 2013
I have following situation -
DECLARE @table1 TABLE
(
Stuid INT NOT NULL,
schoolcd varchar(10),
enterDate Datetime,
entercd char(10)
)
INSERT @Student VALUES
(4648, ‘20130515’, ‘892’,’30’),
(4648, ‘20130103’, ‘764,’10’)
SELECT STATEMENT will give me following -
Stuid schoolcd entercd enterDate
46488923005/15/2013
46487641001/03/2013
Desired output is one row for two or more records for same stuid
464889230 05/15/20137641001/03/2013xxxxxxx/xx/xxxx
How can I accomplish that?
View 5 Replies
View Related
Aug 6, 2004
Hi ,
I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time?
For example if I've two tables involved by join
DELETE <...> from Customers A
INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID
I Must use two statement to remove records from both the tables?
Thx
View 3 Replies
View Related
Jan 26, 2007
Hi there,
I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.
Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?
User tableUserid Username
1 Bob2 Luke3 John
Store procedure resultCompanyname Username
Acrm Corp NullMaxwellarm Ltd Null
Regards,
View 1 Replies
View Related
Apr 18, 2005
It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.
Currently I'll do something like this:
DECLARE @var1 intSET @var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @somekey)DECLARE @var2 nvarchar(20)SET @var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @somekey)
Of course, I'd rather just have to query "table1" just once to assign my variables.
What obvious bit of T-SQL am I missing?
Thank you in advance.
View 2 Replies
View Related
Feb 25, 2008
Hi,
I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.
So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:
select * from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier
So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.
Obviously something like this won't work:
delete from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier
Though it would be nice if it did.:D
So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.
Can anyone render any assistance on this one???
I would certainly appreciate it.
Thanks.
View 1 Replies
View Related
Jun 16, 2006
I am pretty new to the DB part of this but have built an asp.net web appplication with 2 tables:
FORMS and UNITS
I have created a web page that will allow users to add forms and associate a unit with that form. I now need to be able to allow users to associate the form with multiple units.
I can change the web page list box to allow multiple selections but that doesn't solve the problem. This seems like a pretty simple task but I can't seem to find anything on it.
any help???
below is the stored procedure I was using:
CREATE PROCEDURE dbo.USP_AddForm
@UNIT_IDint,
@F_TYPE varchar(20),
@Titlevarchar(100),
@Keywords ntext = NULL,
@Descriptionntext = NULL,
@FileNamevarchar(50)
AS
BEGIN
SET NOCOUNT ON
DECLARE @NewFOrmID INT
INSERT FORMS
(
UNIT_ID,
F_TYPE,
Title,
Keywords,
Description,
FileName,
RevDate
)
VALUES
(
@UNIT_ID,
@F_TYPE,
@Title,
@Keywords,
@Description,
@FileName, GetDate()
)
SELECT @newFormID = @@IDENTITY
SELECT newFormID = @newFormID
END
GO
View 1 Replies
View Related
