Exec Sproc In A Update Function
Feb 5, 2004
Folks
Here is a query which updates certain values. GetAddress is another
sproc which returns addrId. I have to pass certain values ie
strAddress1 strCity .....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in
not a recognized function name. Is the syntax correct to exec sproc
GetAddress.
update Persons
set
Persons.strLastName=H.strLastName,
Persons.strNameSuffix=H.strNameSuffix,
Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4),
Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4)
from ALSHeadr H
where Persons.lngSSN=H.lngFedTaxID
FYI I can post GetAddress sproc but it is working properl.
I just want to know how to pass the values in ALSHeadr table into
the sproc.
Thanx
View 3 Replies
ADVERTISEMENT
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
May 14, 2001
Hello,
I need to get the count into a local variable:
Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause
Exec(@SQL)
It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.
Any help greatly appreciated,
Judith
View 4 Replies
View Related
Feb 6, 2008
And no, not through EM or SSMS
I want to interogate the catalog, so I can hae a job execute and do the grants in case a developer forgets
I mean I guess I can do the all everytime, but I don't know what the impact would be. It appears to be none, because of already done that, but in DB2 that would be bad as it would keep adding rows to the system tables
I have this so far
select *
from syspermissions p
inner join sysusers u
on u.uid = p.grantee
inner join sysobjects o
on o.id = p.id
where u.name = 'mepuser'
and o.name not like 'dt_%'
order by p.id
The M$ catalog is a royal pain
View 8 Replies
View Related
Aug 15, 2005
Hi,I have a sproc with 5 params that takes about 40 seconds to return.But when I Create a Temp table and do aInsert Into #tempExec sproc param1, param2, param3, param4, param5it never returns...any ideas?Thanks,Bill
View 1 Replies
View Related
May 22, 2008
Hi,
I'm trying to capture the value returned from sprocs. I stored the sproc name in the table and use cursor to run each sproc. Now the question is how can I capture and store the return value in a variable?
Here is the scenario:
Table1 has 1 column varchar(50) called vchsprocname
count_A -- procedure, select count(*) from ...
count_B -- procedure, select count(*) from ...
count_C -- procedure, select count(*) from ...
here is my query:
----------------------------------------------------
DECLARE @vchsprocname varchar(50)
DECLARE @count int
DECLARE cur CURSOR FOR
SELECT vchsprocname from table1
OPEN cur
FETCH NEXT FROM cur
into @vchsprocname
WHILE @@FETCH_STATUS = 0
BEGIN
exec @count = @vchsprocname -- I know I cannot do this, the vchsprocname cannot be variable. What else can I do?
FETCH NEXT FROM cur
into @vchsprocname
END
--------------------------------------------------
View 7 Replies
View Related
Jan 20, 2004
CREATE proc dbo.sp_address ( @Abbr char(2) )
as
DECLARE @StateID int
SET @Abbr = UPPER(ISNULL( @Abbr, '' ))
SET @StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @Abbr )
set @StateID=53
IF ( @StateID is null )
INSERT into dbo.States( strAbbr, strName ) VALUES( @Abbr, @Abbr )
if @@ERROR = 0
SET @StateID = @@Identity
return(@StateID)
GO
Can I execute the above stored procedure in a function like this:
create function
sf_GetStateID( @Abbr char(2))
returns integer
begin
declare @StateID int
exec sp_address
return(@StateID)
end
I just want to cross check.
Tks
View 1 Replies
View Related
Oct 15, 2004
I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?
Functions can not call stored procedures, and they can not use temporary tables.
Thanks much
View 1 Replies
View Related
Oct 11, 2007
Hi,
How to exec the following function from my select statement and get my return value:-
ALTER FUNCTION [dbo].[fn_qty]
(@qty decimal (10,4),
@price decimal (10,4),
@pieces int,
@mpt int=0)
returns decimal (10,4)
AS
begin
declare @totamt decimal (10,4)
if @pieces = 0
begin
SET @totamt = (@QTY * @price)
end
else
begin
SELECT @mpt = (SELECT case mpq
when 1 then 10
when 2 then 100
else 100
end
FROM ims.parm)
SET @totamt=(((FLOOR(@QTY)*@PRICE))+(((@qty-FLOOR(@QTY))* @mpt)/@pieces)*@price)
end
return @totamt
end
I can exec the function as exec command as follows:-
exec @totamt( erpinv.dbo.fn_qty 3.5,6,24 )
Best Regards
View 14 Replies
View Related
Feb 1, 2008
In table
tbl_query
(id, sql)
field 'sql'
i have stored sql sentence (select * from ....)
and how can i run this sql sentence?
Exec (select sql from tbl_query where id = 1).
thank you
View 5 Replies
View Related
Apr 7, 2008
Hi,
I've got 2 tables
Table_A with 2 fields: ThreadID & Rating
This Table is updated as users rate forum threads.
Then I have Table_B which should display the some info in a Gridview
Table_B has 3 fields: Name, ID and Score
I want the Rating from Table_A to update to Table_B's Score Field.
I suppose I should write a sproc to do this, but I am not quite sure how;
This is what I would like the sproc to do...
Update Table_B
SET Score = Table_A.Rating WHERE ID = Table_A.ThreadID
Any help on this...or is there a should I not use a sproc.
Also...how do I get the sproc to update the table automatically (without me running the sproc manually)?
View 10 Replies
View Related
Aug 4, 2005
Hi,How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write EXEC nameofstored procedure abc (@abc is the parameter passed).. But How to run a SQL function ?Thanks
View 2 Replies
View Related
Jun 1, 2006
Hi,
How can I do dynamical exec to query in user-defined function? At the end I need to return the result.
Thank's
Alexei
View 1 Replies
View Related
Sep 21, 2007
Hi,
I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.
This is my function:
ALTER FUNCTION [dbo].[ReturnPickItemValue]
(
-- Add the parameters for the function here
@TypeID int,
@CaseID int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @RTN varchar(max)
IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1
BEGIN
SET @RTN = 'SELECT PickItem I +
CASE D.IsStartDateEstimated
WHEN 0 THEN CAST(StartDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)
END +
CASE D.IsEndDateEstimated
WHEN 0 THEN CAST(EndDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)
END
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID
LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID
WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + '
AND P.CaseID = ' + CAST(@CaseID as varchar)
END
ELSE
BEGIN
SET @RTN=
'SELECT I.PickItem
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID
WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + '
AND CaseID = ' + CAST(@CaseID as varchar)
END
RETURN @RTN
END
Each time I try " RETURN EXEC(@RTN) " or something similar I get an error.
I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.
Anyone with any ideas?
View 4 Replies
View Related
Aug 14, 2014
How can I get the data retrieved from the exec function below into Excel
DECLARE @columns NVARCHAR(MAX) ,
@columns_n NVARCHAR(MAX) ,
@sql NVARCHAR(MAX);
SET @columns = N'';
SET @columns_n = N'';
SELECT @columns += N', X.' + QUOTENAME(aaTrxDim)
[code]....
View 5 Replies
View Related
Jun 10, 2008
I have a table with this structure
ID | Ticker
-------------------
1330 |AAB-Bank
1336 |AEGON
1367 |ALZSE
1420 |ASSGEN
2812 |AVLN
I have a sproc called usp_validTicker that will take 2 parameters: ticker and date. It will return the valid ticker for that date.
I like to have the sproc going through each ticker in the table and return the valid tickers.
For example
exec usp_validTicker 'AAB-Bank','2008-6-10' will return 'AAB' and my final table will be
ID | Ticker
-------------------
1330 |AAB
1336 |AEGON
1367 |ALZSE
1420 |ASSGEN
2812 |AVLN
View 13 Replies
View Related
Aug 20, 2007
Does anyone know the system tables I need to query to produce a report of stored procedures (SQL 2005) that had any changes made to them in a user-specified date range?
In SQL 2005, I saw the canned database reports, but this one didn't exist. Any help would be greatly appreciated.
Thanks!
View 3 Replies
View Related
May 18, 2006
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.
Is any easy way to do this?
A database role and then assing that role to the user?
View 4 Replies
View Related
May 13, 2008
I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.
View 23 Replies
View Related
Oct 31, 2014
I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. This would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc:
CREATE PROCEDURE [schemaname].[SetUserCulture]
@UserID int
, @Culture nvarchar(10)
AS
UPDATE dbo.SecUser
SET Culture = @Culture
WHERE UserID = @UserID
execute SQL:
EXEC schemaname.SetUserCulture @UserID = 9, @Culture = N'x'
error:
The UPDATE permission was denied on the object 'SecUser', database 'DatabaseName', schema 'schemaname'.
View 8 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Feb 1, 2015
I have one table1 which contain data like this
ID --- year --- months
1 --- 2015 --- null
table2 like this
ID --- Year --- months
1 --- 2015 --- 6
1 --- 2015 --- 7
I want to update the max value of month for id 1,which is 7 using update function, How can i do this.
View 3 Replies
View Related
Jul 20, 2005
Take a table, where not all the columns are populated:CREATE TABLE #T (A int, B int, C int, D int)INSERT #T (A,B) VALUES (1,2)INSERT #T (A,B) VALUES (3,4)INSERT #T (A,B) VALUES (5,6)INSERT #T (A,B) VALUES (7,8)INSERT #T (A,B) VALUES (9,10)The values for C and D can be computed as functions of A and B. For thisexample, let's say they are twice A and three times B, respectively:CREATE FUNCTION dbo.F(@A int,@B int)RETURNS @Tbl TABLE (X int, Y int)AS BEGININSERT @Tbl (X,Y) VALUES (@A*2, @B*3)RETURNENDNow we use the function to compute the other columns:UPDATE #T SET C=X, D=YFROM dbo.F(A,B)Right? Well, no. Instead, I get this message:Server: Msg 155, Level 15, State 1, Line 2'A' is not a recognized OPTIMIZER LOCK HINTS option.Any suggestions? I would like to use this structure, if possible.Jim GeissmanCountrywide Home Loans
View 7 Replies
View Related
Feb 14, 2008
I have been trying to get a function to UPDATE a row in the database. I know that this is not supported but by using code from a previous post this can be accomplished [See my code below].
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1863258&SiteID=1
Code Snippet
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int GetNextValue(string dbName)
{
int rows = 0;
lock (obj)
{
SqlPipe sp = SqlContext.Pipe;
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand cmd = new SqlCommand("SELECT id FROM Items", conn);
conn.Open();
rows = int.Parse(cmd.ExecuteScalar().ToString()) + 1;
using (SqlConnection updateCon = new SqlConnection("Data Source=" + Environment.MachineName + ";Initial Catalog=" + dbName + ";Integrated Security=True;Pooling=False"))
{
SqlCommand updateCmd = new SqlCommand("UPDATE Items SET id=" + rows,updateCon);
updateCon.Open();
updateCmd.ExecuteScalar();
updateCmd.Dispose();
}
return rows;
}
}
I hope this is helpful.
Regards
tribal
View 3 Replies
View Related
Jan 29, 2008
Can someone please tell me why in the bloody hell this isnt working? It ignores the WHERE VENDORID match portion and marks all instances of USERID match to TRUE. I've been banging my head for an hour... have I really forgotten basic sql???!!!!public static void UpdateVendor(VendorEvaluationEntity VEE)
{int vendorid = Convert.ToInt32(VEE.VendorevalVendor);
int userid = Convert.ToInt32(VEE.VendorevalUser);SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VendorEvaluationConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("Update tblVendorUser set vendoruser_vendor_evaluated = 'true' where (vendoruser_vendor_id = @vendorid) and (vendoruser_user_id=@userid)", conn);SqlParameter pmvendorid = new SqlParameter();
SqlParameter pmuserid = new SqlParameter();pmvendorid.ParameterName = "@vendorid";pmvendorid.SqlDbType = SqlDbType.Int;
pmvendorid.Value = vendorid;
pmuserid.ParameterName = "@userid";pmuserid.SqlDbType = SqlDbType.Int;
pmuserid.Value = userid;
cmd.Parameters.Add(pmvendorid);
cmd.Parameters.Add(pmuserid);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
View 3 Replies
View Related
Jun 28, 1999
In the SQL 7.0 Documentation is indicated that the UPDATE(<fieldname>), if
used in a isert/update trigger, it return TRUE or FALSE based on presence of <field> in the SET List, if the trigger was fired by an UPDATE operation, or in the list of field to fill, if the trigger was fired by an INSERT operation.
Below there is a piece of code that demostrate the false documentation
assertion in the case of a trigger fired by an INSERT operation, infact even
if i have specified only the FIELD1 in INSERT Statement the trigger Print in the screen that i have touched FIELD2 too.
Now the SP1 don't solve this BUG, even if i had read some month ago (before
that SP1 was out) that in SP1 this will be fixed!
Anyone Can Help me to solve this problem, indicating me where i can post the
problem to sensitize the Microsoft's Service Pack Factory ?
Thank in Advance
PS:Sorry for my Bad English .... correct me please if some part are not
readable!
------[Cut Here]-------------------
CREATE TABLE TEST (FIELD1 INTEGER , FIELD2 INTEGER )
GO
CREATE TRIGGER TriggerIU_Test
ON TEST
FOR INSERT, UPDATE
AS
IF UPDATE(FIELD1) PRINT 'Field 1 Touched'
IF UPDATE(FIELD2) PRINT 'Field 2 Touched'
GO
print 'InsertIng '
INSERT INTO TEST (FIELD1) VALUES (2)
print 'Updating '
UPDATE TEST set FIELD1 = 1
GO
DROP TABLE TEST
------[Cut Here]-------------------
View 2 Replies
View Related
Jun 2, 2000
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
BEGIN
DROP TABLE dbo.TestTrigger
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< FAILED DROPPING TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< DROPPED TABLE dbo.TestTrigger >>>'
END
go
CREATE TABLE dbo.TestTrigger
(
colA int NULL,
colB int NULL
)
go
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.TestTrigger >>>'
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
BEGIN
DROP TRIGGER dbo.TestTrigger_i
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< FAILED DROPPING TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< DROPPED TRIGGER dbo.TestTrigger_i >>>'
END
go
CREATE TRIGGER dbo.TestTrigger_i
ON dbo.TestTrigger
FOR INSERT AS
IF UPDATE(colA)
select "updating col A"
IF UPDATE(colB)
select "updating col B"
go
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< CREATED TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< FAILED CREATING TRIGGER dbo.TestTrigger_i >>>'
go
insert into TestTrigger
(colA)
values
(1)
go
insert into TestTrigger
(colA, colB)
values
(2,3)
go
View 1 Replies
View Related
Mar 14, 2000
I have an application which uses DTS to import data. After the data is imported the transaction logs are no good since the import wasn't logging.
Is there a way to turn logging on?
Thanks
View 1 Replies
View Related
Mar 8, 2004
I am trying to write an update statement based on an aggregate and it will not let me. Please find below the SQL.
update abtimesummary
set hours = sum(a.hours)
from abtimestore a
join abtimesummary b
on (cast(a.weekno as varchar(10)))+'-'+(cast(a.empno as varchar(10))) = b.summaryid
and this is the error message:
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
Can someone tell me how to get round this please?
Many thanks
View 2 Replies
View Related
Jul 23, 2005
Table test2 has multiple amounts for each account, I would like to sumthe amounts for the same account and use the result to update thevariable 'tot_amount' in table test1. But SQL does not allow me to usesum function in update. Is there any other way to do this? Thanks.update test1set tot_amount=sum(b.amount)from test1 as ajoin test2 as bon a.acc_no=b.acc_no
View 4 Replies
View Related
May 22, 2008
Here is the example:
I have two tables. One has Projects with the total amt of hours worked on the project itself. The other is an Employee_Projects table with individual rows of hrs per employee worked on the above referenced projects.
I need to SUM all the hrs from the Employee_Projects table and GROUP BY project number, then UPDATE the Projects table with the sum of hours where the Project Number from table A matches the Project Number from table B.
Of course, you cant use an aggregate function in an UPDATE clause, so what would be the easiest way to do this??
Any help would be much appreciated.
-C
View 2 Replies
View Related
Jan 22, 2004
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
Updated
THN 000000092637 100 A300
THN 000000092637 200 B300
THN 000000082455 100 A300
THN 000000082455 200 B300
THN 000000051779 100 A100
View 4 Replies
View Related
