Sproc/Insert query copying one value into another field

Sproc/Insert query copying one value into another field

Post by Anthony Fletcher Roger » Fri, 04 Jan 2002 01:29:48



I administer a document management database and have a requirement to
copy the numerical value ( the document number) from one field into
another custom field in each row. For new documents I have written a
trigger to achieve this, but I am having trouble doing this for existing
documents. As a bit of a newbie, I can't find a way to make the query
pick up the correct value, instead it inserts the first docnum value it
finds, and it then puts this into the custom field in all the rows! Here
is the query:



update mhgroup.docmaster

Many thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Sproc/Insert query copying one value into another field

Post by Steve Kas » Fri, 04 Jan 2002 01:41:11


Anthony,

  If

    select docnum from mhgroup.docmaster



guaranteed to be one of the values returned by the query,
but you don't know which one.

If you assign using SET instead of SELECT, it's safer, since
you'll get an error if you try to assign more than one value:


I assume you need a where clause in this select, and probably
some change to the update query, since it will set cdbl1 to the
same value for every row in the table.

To copy one column to another (I'm not sure why you would need
two identical columns, but it sounds like you are saying that), you
can do it for every row in the table as

update mhgroup.docmaster
set cdbl1 = docnum

Steve Kass
Drew University


> I administer a document management database and have a requirement to
> copy the numerical value ( the document number) from one field into
> another custom field in each row. For new documents I have written a
> trigger to achieve this, but I am having trouble doing this for existing
> documents. As a bit of a newbie, I can't find a way to make the query
> pick up the correct value, instead it inserts the first docnum value it
> finds, and it then puts this into the custom field in all the rows! Here
> is the query:



> update mhgroup.docmaster

> Many thanks

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!


 
 
 

Sproc/Insert query copying one value into another field

Post by Anthony Fletcher Roger » Fri, 04 Jan 2002 02:09:41


Many thanks, that sorted out the problem. I was looking for a too
complicated solution!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Returning field value of query in a sproc

Hi,

I was wondering how do I use the Output variables of a sproc
to output a field value of a SQL query I made,
I can't seem to assign a recordset field to a variable.seems the recordset
is gone after the sql query.

Example:

CREATE PROC spGetShipPrice


AS
 SELECT Distinct Price_First, Price_Additional
 FROM ER_ShippingPrice



In the calling code I am interested in getting only the two output
variables,
I don't want to get a whole recordset (too much overhead & useless).

Thanks...

2. Singapore - Oracle Financials

3. How Do I copy one field value to a string variable

4. Need with a query

5. insert data from one field in one table to another field in another table

6. Query Suggestions (Tom)

7. Calling Sproc from Sproc and getting only one recordset

8. ct-lib callback message

9. Copy Datetime Values from one Column to another before 2nd Columns values

10. optionally inserting column default values in a sproc?

11. sproc: Find duplicate value first before insert

12. how to copy and paste several fields from one file to an other one

13. Combining Insert Values and Insert Select Statements into one Clause