Using The Result Of A SELECT As A Vaule To UPDATE With

Jun 13, 2005

Hi AllI hope some1 with more experience of Sql Server 2000 Stored proc's can help me or point me to somewhere I can find the info cos at the moment it's doing my head in, I have spent 2 day,s so far trying to solve the following query, the problem is I do not understand the errors I am getting or why so here is the code that won't work

ALTER PROCEDURE  dbo.UpdateStock

@OrderID intASSELECT    (dbo.Products.Stock - dbo.OrderDetails.Quantity) AS NewStockFROM         dbo.OrderDetails INNER JOIN dbo.Products ON dbo.OrderDetails.ProductID = dbo.Products.ProductIDWHERE     (dbo.OrderDetails.OrderID = @OrderID)                          UPDATE    dbo.Products                           SET  dbo.Products.Stock = NewStockWhat I am trying to do is get the OrderDetails.Quantity form the  OrderDetails table the UPDATE  the Stock feild in the Products Table by deducting the  OrderDetails.Quantity  From the Products.Stock  to give me the new stock levelWell it works to an extent in that if I do this to  SET  dbo.Products.Stock = 10 it will put 10 in the Stock feild but I am unabke to get the value of  NewStock , which caluculates the correct value into the set statment I keep getting invalid colum name, I have tried any combination I can think of but I still get error of 1 sort or another.So if any1 could help as I am pulling my hair out with this 1So perhaps some1 could cast there eye over it pleaseThanks

View 2 Replies


ADVERTISEMENT

UPDATE Records In 1 Table With Result Of Select Statement

Jun 12, 2014

I want to update records in 1 table with the result of a select statement.

The table is called 'MPR_Portfolio_Transactions' and contains the following fields:

[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]

And the select statement is like this:

SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE

This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'

the key is PTR_SEQUENCE

View 3 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Result Sets Using Select In Query Anlyzer Vs BCP Vs Select Into

Jul 9, 2002

When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks

View 1 Replies View Related

Sum Result Of A Select

Jun 21, 2007

In the database i have a table with this information:key_id =1key_desc =43+34+22+12I want sum the values in key_desc. Something like:SELECT key_desc FROM tableBut the result of the select was "111" and not "43+34+22+12".Is this posible?

View 6 Replies View Related

How To Do A SELECT On SP Result

May 10, 2006

Hi there,

Problem:

Having an old webapplication with dynamic build sql queries. Now the database changed completly because of new db model. Queries are getting much more complex now, so i put them into stored procedures.

Now i want to work with the data in an application for sorting an filtering. All the 'WHERE' conditions are build dynamic within the code. For not modifying the whole application i would like to do something like:

SELECT * FROM (EXECUTE sp_someprocedure) WHERE X = 1 

Is there a way to realize this ?

 

Thx for helping

View 5 Replies View Related

Select All = No Result

Jan 30, 2007

hey there

I have parameters in my report set up

CallStatus = string

Available Values are non-queried and values are listed in the value list

closed, open, pending, select all

If I choose each one separately I get a result - if I choose select all I get no result.

This worked fine attaching to another datasource - I have now attached it to a new source.

Tried altering the relationship to properties to a left outer join but this made no difference.

Can someone tell me what else I should check?

thanks

View 6 Replies View Related

Update Statement Works Ok But No Result

Nov 24, 2005

here is my update statement in a stored procedure:

create proc proc_add_comp
    @comp_answer nvarchar(300),
    @admin nvarchar(100),
    @comp_id int
as

update tbComp set
   
comp_answer = comp_answer + ' - ' + @admin + ', ' + @comp_answer

where comp_id=@comp_id

then I try it like this :
exec proc_add_comp 'new answer','by me',1

result is : (1 row(s) affected)

but when I look in the db, nothing was changed, comp_answer still has its old value..
comp_answer is nvarchar type column..isnt add operation allowed in update statement?
thanks...

View 1 Replies View Related

Update Columns With Rows Result

May 26, 2008



Hi,

for example i need to update table with top 5callers who called the most to one number:

With subselect i get results:

Number Caller Times

555-11111 555-11112 10
555-11111 555-11113 9
555-11111 555-11114 8
555-11111 555-11115 7
555-11111 555-11116 6

And now i need update table and get this results:

Number Top1Caller Top1Caller Top1Caller Top1Caller Top1Caller
555-11111 555-11112 555-11113 555-11114 555-11115 555-11116

And i need to do this with every number in database

View 1 Replies View Related

Conflict In SQL Select Result S

Jan 21, 2002

Hi,

I ran a sql somedays ago it gave me result, I ran the same sql today it gave different result. The only difference is that the particular table in the sql is frequently used table with lots of new insert. But the sql I ran is not selecting the new rows, it only deals with already inserted records.

Any idea what will make the sql to give different result from same set of records in different time.

Thanks
John Jayaseelan

View 3 Replies View Related

Select 'static' Row/result

Oct 17, 2006

Let's say I have a simple query to return the results of my "Status" table. The query reads as follows:


Code:


Select statusID, statusName
From Status



Here is the result set that I am returned:


Code:


22 Associate Member
23 Is Not Paying
24 Exempt
25 Fair Share
26 Member
29 Retiree
30 Staff
32 Fair Share - Self Pay
34 Member - Self Pay



Now, I am using this query for reporting purposes and would like to inject some additional sql that will append one additional row to my result set -- this is what I am calling the 'static' row in the thread title.

In other words, without modifying my database I would like to return the above set of data but with one additional row that has an arbitrary ID with the name "Unknown" or something similar.

again, I want to avoid adding an "Unknown" field directly to my database -- is their any way to "hard code" the selection of this row in my sequal?

Thanks,

Zoop

View 1 Replies View Related

Add Index To Select Result

Mar 20, 2007

Eliko writes "i would like to add a coloumn to a select result, so there will be another coloumn with indexed running numbers for each record.
how can i do it?

thank you
eliko"

View 1 Replies View Related

Different Result Of Each Select Statement

Mar 14, 2008

Hi, I execute a select statement

SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'

every times I execute it I get different result.

1st result : 17036986
2nd result : 17037903
3rd result : 17038309

Any idea??? There is no any inserting on TABLEA, so it should not has changes. Is that because of statistic is still updating?? I don't have much knowledge on this so I cannot sure the cause of the problem. Please advise.

View 18 Replies View Related

How Do I Select Last 10 Rows Of The Result Set??

Jun 13, 2006

Can we select "last 10 rows" from the result set. (sql server 2000)

Something of an opposite of "top" functionality

View 3 Replies View Related

How To Use The Stored Procedure Result To Update The Table

Feb 11, 2008

Hi,

I am new to stored procedure.

My stored procedure is returning me the list of tables names where the given tables PK is used as FK (can be null), based on the result of stored procedure I need to update the tables.


Following Stored procedure will return the list of tables where the usertable's PK is referred as a FK

I want to use the returned table name and update the set eh FK's value = null.

Some how it is not working , can some one point me out on correct solution.



CREATE PROCEDURE [dbo].[sp_delete_data]
AS
DECLARE @update_Var table (
REF_Table nvarchar(50),
FK_Column varchar(25),
PK_Table varchar(25),
PK_Column varchar(25),
Constraint_Name varchar(50));

insert into @update_Var
SELECT
FK.TABLE_NAME,
CU.COLUMN_NAME,
PK.TABLE_NAME,
PT.COLUMN_NAME,
C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN(
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME

WHERE PK.TABLE_NAME='usertable';

Select * from @update_Var



Thanks
Santosh Maskar

View 7 Replies View Related

Get Numeric Result From SELECT Statement

Jun 11, 2008

Hi,I have this code following my signature to SELECT a number.I don't know what am I missing, because it always return the number which the variable banID was initialized (32 in this case).The strangest thing is that if I run the statement inside MS SQL SERVER, I get the right result.Any help would be appreciated.Warm Regards,Mário Gamito--// Get inserted user IDint banID = 32 ;SqlConnection myConn = new SqlConnection("Data Source=192.168.1.6; Initial Catalog=db1; User=sa; Password=secret");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlCommand myCommand = new SqlCommand("SELECT MAX(PublisherID) FROM table1", myConn);banID = Convert.ToInt32(myCommand.ExecuteScalar);}catch (Exception e){ Console.WriteLine(e.ToString());}Response.Write(banID);

View 3 Replies View Related

How To Encrypt Result Of Select Statement

Jun 6, 2012

I want to export the data from a database and place it on a csv file to be imported to another database. However, I want some columns from the selected data (result of select statement) to be encrypted. What should I do? Is this possible? How do you decrypt the data during import?

The original data is from an MS SQL database to be transferred to a PostgreSQL database.

MS SQL ----- CSV (some columns are encrypted) ---- PostgreSQL (all columns decrypted)

I posted the same question on the mysql category. I wanted to delete it since I just realized today that I posted it on the wrong category but seems like there is no way I can delete.

View 2 Replies View Related

SELECT Result Without Index Sorting

Mar 16, 2004

I want to SELECT the result from table, but i want the result return in record entry order, instead of sort by index or ORDER BY certain field.

View 14 Replies View Related

SELECT Result In Inserted Sequence

Mar 16, 2004

I have one table with primary key index, when i want to select the result from this particular table, i wish the result arrange by record inserted squence and not by the primary key i had been set.

View 4 Replies View Related

Saving Select Result To A Table

Jun 21, 2014

I have the select statement below - where I use some external functions - and I would like the result to be saved to another table.After executing the statement I end up with a table containing the following columns:

accountno
d.date_start
d.date_end
TWRR_Month
TWRR_Cum

Normally I would use the command 'INTO TableName' but since the statement is so long I don´t know where to place it.The select statement is as follows:

IF OBJECT_ID('tempdb..#trn') IS NOT NULL
DROP TABLE #trn

IF OBJECT_ID('tempdb..#mv') IS NOT NULL
DROP TABLE #mv
SELECT PTR_sequence as trno, PTR_CLIENTACCOUNTNUMBER as accountno, PTR_DATE as date_trn,
CASE PTR_TAC
WHEN 'BUY' THEN 0
ELSE PTR_LOCALAMT
END as amt_trn

[code]...

View 2 Replies View Related

Assign SELECT Result To Variable

Jun 7, 2007

Hi this is probably a very stupid question, but I still need to know.

How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?

View 3 Replies View Related

Select Distinct - Incorrect Result

Jul 20, 2005

I have a function that is designed to return a variable that containsconcatenated values from a partinular field in the returned rows:DECLARE @output varchar(8000)SELECT@output =CASEWHEN @output IS NULL THEN CAST(TSD.ScheduledTime ASvarchar(4))ELSE @output+ ', '+ ISNULL(CAST(TSD.ScheduledTime ASvarchar(4)),'')ENDFROM TSDWHERE ClientGUID = 2000001447020001 ANDParentGUID = 6000006684068001Select @outputThe variable returned with this code contains:"1200, 1400, 1200, 1400"I want to only get the unique values so that the variable returns "1200,1400". Seems simple enough just to add DISTINCT to the SELECT statement.However, what is returned is simply "1400".I cannot figure out why that is the case. Is there any explanation to thisresult?Side note: I can work around this by using a cursor but I would like toknow why DISTINCT does not work.Many thanks in advance for any help that can be provided!Pat

View 5 Replies View Related

How To Select In From A Store Procedure Result?

Feb 26, 2007

HI, I'm a simple store procedure that returns a result such as this one:

AM
AM-1
AM-2
AM-n

and in other store procedure I need to filter result from this list.
I think that some query like this is impossibile

select fields from table where id in (execute sp)

how can I make this?

Thanks a lot.

View 4 Replies View Related

Storing The Result Of A Select TOP 1 Into A Variable

Sep 7, 2007

hello

Does anybody know how to store the result of a select top 1 into a variable??

I have this code:
Select @status = Select top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc


And also this:
Select @status = top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc

But none of them work


Any ideas??
Thanks

View 1 Replies View Related

Working On The Result Set Of Select Query

Sep 20, 2007



Hi
I have a table as follows

Table Cats
{

catergory varchar(20)
Update datetime
}


And the data in the table is as follows

Category Update
------------- --------------
cat1 d1
cat2 d2
cat3 d3

I would like to get only 'Category' in result set and work on it ( similar to foreach in C# )

select Category from Cats will return the required result , but how can i iterate thru then in T-Sql
Can any one please throw some light

Thank you
~Mohan Babu


View 1 Replies View Related

Can You Check My Select Max Query ? The Result Not Corret...

Aug 1, 2007

in table Databackup
company       keepmonth
-----------------  -------------------
001                 12002                 12003                  6005                  607917              609747              6
 
I run this query
select Max(keepmonth) as keep from Databackup
why the result is 6 not 12? I think the max value should 12 , have no idea why it return 6
does my query error?
thank you

View 1 Replies View Related

Aggregate Function For Select Statement Result?

Oct 19, 2004

Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.

currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.

Problem:
i need to call an aggregate count on the results of another aggregate function (sum) with a group by.

*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales


This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.

thanks,
Tom Anderson
Software Engineer
Custom Business Solutions

View 3 Replies View Related

How To Use Variable To Receive The Result Of A Select Statment. ?

Jun 21, 2002

Good morning;

My Problem is :im my transaction i use insert code like the following :

" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"

In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.

" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "

I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.

Thinks.

View 1 Replies View Related

SELECT DISTINCT Will Always Result In A Static Cursor

Jul 31, 2007

an example for the pb
1)First i have created a dynamic cursor :

DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor

2)The result for this cursor is for expamle 'USA'.

3) If now i do an update on that location with a new value 'USA1'

update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7

4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).

If i remove DISTINCT from the cursor declaration , the process works fine .

View 10 Replies View Related

Return A Result Set From A SELECT Query In A Function?

Jan 21, 2008

Hello all:

How can I return the result of a SELECT statement from a stored procedure (function)?

CREATE FUNCTION returnAllAuthors ()
RETURNS (what do i put here??)
BEGIN

// Is this right??
RETURN SELECT * FROM authors

END


Thanks!

View 12 Replies View Related

Row Count Returned By A Select Query In Result

Jul 30, 2013

I want to show the number of rows returned by a select query in the result.

e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy

If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result

OutPut

0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..

How can i get this number in the result.

View 3 Replies View Related

Select Query - How To Get Result Based On Given Table

Sep 14, 2014

Till now I get data form multiple table using join, but unable to understand how can i get the this result based on given table -

Result should be -

ProCodeProductName
PRO00001;PRO00002Product Test SearchedPromotion One;Promotion Two
PRO00001;PRO00002;PRO00002Product Final SearchedPromotion One;Promotion Two;Promotion Three
PRO00002TestingPromotion Two

Tables -
select * from ProMaster
CodeName
PRO00001Promotion One
PRO00002Promotion Two
PRO00003Promotion Three

select * from ProDetail
IDProCodeProduct
1PRO00001;PRO00002Product Test Searched
2PRO00001;PRO00002;PRO00002Product Final Searched
3PRO00002Testing

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved