I recently had an interview and the interviewer has asked me to use a group by clause in an update statement. Here is the table and data:
Table name: Customer
cust Size Geo Segment
BP LG EU
Accenture LG US
MSC MED US
SclumbergerLG US
So the request is to update the Segment column with a unique number starting with number 1.
So for BP it should be 1, Accenture it should be 2, MSC it should be 3, Sclumberger should be 1 since the size and geo are not unique. He asked me to use a group by size and geo to generate a unique number for each combination and than make the update.
I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €œgroups€? and enter a Budget value = BudgetTotal/Count.
How could I write this in a stored procedure or a SQL statement if possible?
Thanks.
Kevin
SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1 FROM dbo.ReportTable INNER JOIN dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget HAVING (dbo.ReportTable.Budget < 1) ORDER BY dbo.ReportTable.ProjectNo
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
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
I have a little problem concerning a SQL statement I need to do.
here is my statement:
SELECT Location, [Week of Availability], GSDC_Name, PA_Name, CA_Name, MAX([Year of Availability]) AS [Year] FROM dbo.tbl_availability GROUP BY Location, GSDC_Name, PA_Name, CA_Name, [Week of Availability]
now the problem in this statement is that I don't want to group by Week of Availability, but I do want to select it. BUT, if I remove Week of availability from the Group by part, I get an error! how can I do this?
A client wants to keep track of the number of searches for keywords ina date range. So, I'm storing each occurance of a search in a table.The columns are:PK: idsearch_stringsearch_dateI'm trying to wrap my head around how I would select the number ofoccurances for each string, divided by days. The desired result wouldlook something like:search_date search_string numOccurances----------------------------------------------------------March 20 dogs 4March 20 pigs 2March 21 dogs 8March 22 pigs 3March 22 pigeons 5I've tried a query like:selectsearch_string, CONVERT(CHAR(11),search_date,106) as search_date,count(search_string) as numOccurancesfrom searcheswhere search_date >= dateadd(d,-3,getdate())group by search_date, search_stringbut it doesn't give me the desired results. I'm sure I'm just lookingat it the wrong way.Suggestions?Thanks!
I need this to work:SELECT [ID] FROM [test] WITH (NOLOCK) where [a/c/d]='a' GROUP BY [unit#],[EFF DATE] HAVING COUNT ([unit #]) > 1The problem is that I get an error that [ID] needs to be in the GROUPBY clause or aggregate function. if I put it in there, I will get noduplicates (because it is the identity field). The whole point ofthis is to find dups.Thanks for any help.Robby
I am working on some sql for a report and i am getting an error with the group by statement.
SELECT Member.memberNo, Member.FamilyName, Member.GivenName, Member.gender, Membership.FeesPaid, (Membership.Fees - Membership.FeesPaid) AS OutstandingFees FROM Membership INNER JOIN Member ON Member.memberNo = Membership.memberNo GROUP BY Member.registrationNumber;
This fails because of the GROUP BY.
Does anyone know why this is the case?
This is the error that is produced: SELECT DISTINCT Member.memberNo, Member.FamilyName, Member.GivenName, Member.gender, Membership.FeesPaid FROM Membership * ERROR at line 1: not a GROUP BY expression
If someone could help this would be greatly appreciated.
Hello everyone, I'm trying to practice some SQL statements from my Web Data Administrator. Until now everything has worked fine. However, when I write the following statement: SELECT DATEPART(m, Date) + '/' + DATEPART(yyyy, Date) AS Month, COUNT(*) AS NumberOfEvents FROM CompanyEvents GROUP BY DATEPART(m, Date) + '/' + DATEPART(yyyy, Date) I get the following error " Server: Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value '/' to data type int." Do I have to change something on the database? Now, I tried to google the msg 245 and I cannot get any clear answers on the topic. I would greatly appreciate if someone could give me some guidance on this problem. Thanks, Eduardo
I have the following SQL Statement: SELECT CONVERT(char(10), FixtureDate, 101) AS Date, COUNT(*) AS 'NumberOfRecords'FROM tblFixturesGROUP BY CONVERT(char(10), FixtureDate, 101) I want to add a new column called "need results". This column needs to be count if a certain cell is NULL. Count If HomeScore IS NULL as well as grouping by date and counting the number of records. So the third column needs to count the number of records where homescore IS NULL
The GrossSaleAmount and NetSaleAmount are calculated fields. But for this post, kindly ignore why I am storing calcuated fields...
QUESTION: What I want to do is to populate another table (the DDL of which is give below) from tblSales in such a manner that the TOTAL sales from each product for each available date is grouped/summed together.
I want to run a much larget SQL statement, but for examples sake this is a good starting point
Code: Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo
It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statement?
Hi guys, I am having a little diffulty displaying two columns next to each other in a case/group by statement as code shown below. I was wondering if i could have the results displayed such that the gst_amount and total_amount are in two separate columns (as they currently are) however the results of the rows are in the same row not in separate rows (as they are currently). I dont think i can do this in the current case statement that i have (i.e: the two case statements). Any feedback would be appreciated
SELECT distinct PERIOD.STARTDATE, temp_111.EVENTTYPEID, case when temp_111.[name] like '%GST%' then sum(temp_111.CONTRIBUTIONVALUE) end as GST_AMOUNT, case when temp_111.[name] not like '%GST%' then sum(temp_111.CONTRIBUTIONVALUE) end as Total_Amount FROM temp_111 INNER JOIN PERIOD ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ WHERE (NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL) AND temp_111.PERIODSEQ in ('111') group by PERIOD.STARTDATE, temp_111.EVENTTYPEID, temp_111.[name]
Hi,I am bloody amateure and I was wondering if someone could help me editthe statement below so it groups the field SCDMASTER.SCTY_CLASS_CODEand creates one field called "total balance" equallingCUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNTSELECTCUSTODY_BALANCE.APPLICATIONCYCLEDATE,CUSTODY_BALANCE.ASSET_ID,SCDMASTER.SCTY_CLASS_CODE,CUSTODY_BALANCE.OPENING_BALANCE,CUSTODY_BALANCE.DEPOSIT_AMOUNT,CUSTODY_BALANCE.WITHDRAWAL_AMOUNTFROMFPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,CAPSREPORT.SCDMASTER SCDMASTERWHERECUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND(SCDMASTER.SCTY_CLASS_CODE Not In('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))Thanks,Andreas
Hi Experts, I would like to make a stored procedure in my db: I have sql express 2005. I get Error 164 when creating this procedure:
CREATE PROCEDURE CrossTable
@Variable1 smallint,
@Variable2 smallint,
@Value smallint
AS
BEGIN
SELECT @Variable1, COUNT(@Variable1) AS 'Haufigkeiten'
FROM SurveyData
WHERE @Variable2 = @Value
GROUP BY @Variable1
END
GO
I would like to generate a frequency chart per userdefined-variable (@variable) with a where restriction.
The GROUP BY @Variable1 seems to be problem: is there some workaround in order to use variables in a GROUP BY clause? or how can I write an sql statement which do the same as this procedure (CrossTable) without using the GROUP BY clause?
Hi, I am having trouble getting my query right. i am using a stored procedure to retrieve this data and bind it to my grid view. Problem: I can't associate a field with a column that i am returning with my record set. Details: I have query that i want to return 9 columns (UserName, Pre-Approved, Processing, Underwriting, Conditioned, Approved, Docs Out, Docs Back, Conditions Met). The username column lists all loan agents. I want the other 8 columns to list the name of the borrower (crestline..borrower.lastname) that is associate with that loan agent and that loan state. Each time a record is found where there is a valid loan agent (UserName) that meets the 'where' conditions, there will be a borrower. the 'LoanKey' field is a primary key that lets me get the borrower name from the borrower table. I dont know how to construct my query such that those borrower names get associated with their respective column header. if the query works, my Gridview should look like this ('Name' refers to the borrower name) UserName | Pre-Approved | Processing | UnderWriting | Conditioned | Approved | Docs Out | Docs Back | Conditions Met Bob | | | | Name | | | | Bob | | Name | | | | | | Bob | | | | | | | Name | Steve | | | Name | | | | | Steve | | | | | | Name | |
Here is my sql call: SELECT cfcdb..users.username, crestline..borrower.lastname,CASE WHEN crestline..loansp.LoanStatus='Pre-Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Pre-Approved',CASE WHEN crestline..loansp.LoanStatus='Processing' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Processing',CASE WHEN crestline..loansp.LoanStatus='Underwriting' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Underwriting',CASE WHEN crestline..loansp.LoanStatus='Conditioned' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditioned',CASE WHEN crestline..loansp.LoanStatus='Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Approved',CASE WHEN crestline..loansp.LoanStatus='Docs Out' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Out',CASE WHEN crestline..loansp.LoanStatus='Docs Back' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Back',CASE WHEN crestline..loansp.LoanStatus='Conditions Met' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditions Met'FROM cfcdb..usersinner join (crestline..loansp inner join crestline..borrower on crestline..loansp.loankey = crestline..borrower.loankey)on crestline..loansp.fstnamelo=cfcdb..users.firstname AND crestline..loansp.lstnamelo=cfcdb..users.lastnameinner join cfcdb..users_roleson cfcdb..users.username = cfcdb..users_roles.usernamewhere cfcdb..users.active = 1 AND cfcdb..users_roles.groupid = 'agent'AND crestline..loansp.enloanstat <> 'Closed' AND crestline..loansp.enloanstat <> 'Cancelled' AND crestline..loansp.enloanstat <> 'Declined' AND crestline..loansp.enloanstat <> 'On Hold'order by cfcdb..users.username asc
I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one. The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits': ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83 And run the query: select max(Cost),FruitName From Fruitsgroup by FruitName It'll correctly return: FruitName CostApple 0.63Pear 0.89 Now i need the ID also returned by my query so i go: select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID This doesnt return the above results with the ID appended to it, it instead returns: ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83 As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of: ID FruitName Cost2 Apple 0.634 Pear 0.89 Thanks.
Item Color Quantity -------------------- -------------------- -------------------------- Table Blue 10 Table Red 20 Table Yellow 30 Chair Blue 40 Chair Red 50
I'm wondering if there is a group state like this: Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item which returns this:
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
I am currently straching my head as to why the following doesn't work as I expect. I have Three tables UsersManagers, Users and Bookings:-
SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours FROM UsersManagers LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username WHERE UsersManagers.UsernameManager = 'testPM1' AND (Bookings.DateOfBooking BETWEEN '01 October 2006' AND '31 October 2006') GROUP BY UsersManagers.UsernameUser
This statement returns only one username with the hours they have booked in my Bookings table. However the left outer joins (I would have thought) should return an entry even if there are no hours booked for a user. If I omit the Date Section:
SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours FROM UsersManagers LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username WHERE UsersManagers.UsernameManager = 'testPM1' GROUP BY UsersManagers.UsernameUser
I get two records returned testUsr1 37.5 testUsr2 NULL
I'm trying to calculate some leagues for a website i run usuing some quite complicated querys. As the leagues are calculated with such somplicated criteria, i've had to build the leagues inot a temporary table.
What i need to be able to do is update the temp table with the results of a group by query. This gives me an error and the only way i have found to achieve this is to insert the results of a group by into another temp table, and then use that to perfom the update.
Is there something i'm missing, or is this the only way to achieve my goal ?
My first post on the forum, I wish I had found this place sooner, looks to be full of good advice and knowledgeable posters.
I have tried searching and looking at the FAQ's but couldn't find an answer. So here goes, all help greatly appreciated.
SQL Server 2005
Table (many columns left out for simplicity)
ID - unique. HdrID - key to the header record. PTtimestamp - date, date/time the row was written to DB. PType - integer, representing various states of the row. etc etc etc (another 15 columns of data)....
Typically there is approximately 250 records per HdrID.
I am trying to do an SQL UPDATE without using my usual solution of writing vb code!
I want to update the PType of latest row (as per timestamp) for each HdrID to -9999.
and now I need to update the field col1 of the first records with different col2 value. that means I need to update the col1 filed of the * marked record.
any suggestion about how to do it with SQL server 2000?
I'd like to update the attribute Active of the table COMPANY_BRANCH to the value 0 (or false, since it is a bin field) on the below recordset:
SELECT COMPANY_BRANCH.CompBranchID, MAX(PLACEMENT.ExpirationDate) AS Expr1
FROM COMPANY_BRANCH INNER JOIN
PLACEMENT ON COMPANY_BRANCH.CompBranchID = PLACEMENT.ProducerBranchID
GROUP BY COMPANY_BRANCH.CompBranchID
HAVING (MAX(PLACEMENT.ExpirationDate) < DATEADD(yyyy, - 1, GETDATE()))
yet, I am unable to understand the concept on how this can be done. If I put it into query designer and change the query type to update, the code that gets generated is not correct - can someone help me?
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL WHILE @@ROWCOUNT > 0 BEGIN SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL END SET rowcount 0
I'm trying to update a varchar field using SUM. I keep getting the error that the sub query returns more than one value.
UPDATE CIRSUB_M SET TRM_DMO = SUBSTRING(TRM_DMO,1,11) + (SELECT CAST(SUM(COPIES) AS VARCHAR(5)) FROM CIRSUB_M WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T') GROUP BY PUB_CDE, DNR_NBR) WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')
Example
PUB_CDE DNR_NBR COPIES TRM_DMO THN 000000092637 100 A THN 000000092637 200 B THN 000000082455 100 A THN 000000082455 200 B THN 000000051779 100 A
I'm bulk loading employees into an etl table, each employee has a unique ID number, but they have multiple records in the data. Sometimes their name or birthdate will change and I want to identify those records and only insert the newest version into production. I can do this with a series of temp tables, but I'm sure there's a better way. The SQL below updates the etl table with the flag I want to mark the inserts, but it seems convoluted. (Jon's birthday changes, Jane's birthday changes, Bill's gender changes, Amy nothing changes(I handle those inserts later))
