Update Column Of A Table With A Sproc
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
ADVERTISEMENT
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
Jun 14, 2007
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies
View Related
Oct 6, 2014
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
View 2 Replies
View Related
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
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
Sep 19, 2012
Need to update one table with value of a column in another table plus a constant:
UPDATE TABLE_A
SET TABLE_A.COLA=TABLE_B.COLB+'10'
FROM TABLE_A
INNER JOIN TABLE_B
ON TABLE_A.COLA=TABLE_B.COLA
WHERE TABLE_A.COLA=TABLE_B.COLA
The above statement works except the concatenated string has space between TABLE_B.COLB text and '10'. How to remove the space (4 characters) so that the string shows as one word 'TABLE_B.COLB10'?
View 2 Replies
View Related
Jul 8, 2014
I have to tables say 'employee1' and 'employee2'
employee1 has lastname
employee2 has firstname, lastname
i have to update firstname in employee1 from firstname in employee2 table and the common field for both tables is 'lastname'
View 8 Replies
View Related
Sep 2, 2005
Hi, I have two tables. I want to update two columns in my first table,[ADD_BSL_SALES] and [ADD_BSL_COST] with two values [Sales] and[Costs] held in my #temp table but based on a RUN_DATE from my firsttable.Can anyone point me in the right direction?Thanks in Advance ï?ŠBryanCREATE TABLE [GROSMARG_AUDIT_ADDITION] ([RUN_DATE] [datetime] NULL ,[SALES_DIFF] [numeric](19, 6) NULL ,[COST_DIFF] [numeric](19, 6) NULL ,[ADD_BSL_SALES] [numeric](18, 0) NULL ,[ADD_BSL_COST] [numeric](18, 0) NULL ,[ADD_SALES_DIFF] [numeric](18, 0) NULL ,[ADD_COST_DIFF] [numeric](18, 0) NULL) ON [PRIMARY]GOINSERT RUN_DATE,datetime,INSERT SALES_DIFF,numeric(19,6),INSERT COST_DIFF,numeric(19,6)INSERT ADD_BSL_SALES,numeric(18,0),INSERT ADD_BSL_COST,numeric(18,0),INSERT ADD_SALES_DIFF,numeric(18,0)INSERT ADD_COST_DIFF,numeric(18,0)--- Second TableCREATE TABLE #DUPTOTALS[Sales][Costs]
View 1 Replies
View Related
Jul 20, 2005
I need to update a table by copying a column from another table(having the samestructure, but on another database), from the record having the sameprimary key.1 - What is the correct query?2 - I tried copying them record by record, but the datatype is ntext,(it displays <longtext> in the result pane), and trying to update it results in thefollowing errormessage:The text, ntext, and image data types are invalid in this subquery oraggregateexpression.I tried variations of the following:UPDATE TABLESET column0 = (SELECTcolumn0FROManotherDB.dbo.TABLEWHEREanotherDB.dbo.TABLE.column1 = column1)WHEREanotherDB.dbo.TABLE.column1 = column1
View 1 Replies
View Related
Mar 25, 2008
hello all,
I don't know how to update table A with data from table B without specifying every column.
These two tables have the same fields and same structure.
I know that it's possible to do the following:
update table A
set A.name = B.name,
A.job = B.job
from table B
But I have many columns and don't want to describe every column, is that possible?
Thanks!
View 6 Replies
View Related
Sep 25, 2007
Is it possible to update from one table to another?Pls examine my code here:
UPDATE tStaffDir SET tStaffDir.ft_prevemp = ISNULL(tStaffDir_PrevEmp.PrevEmp01, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp02, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp03, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp04, ' ') + ' ' + ISNULL(tStaffDir_PrevEmp.PrevEmp05, ' ') Where tStaffDir_PrevEmp.ID=tStaffDir.ID
I am trying to concatenate the columns from tStaffDir_PrevEmp to tStaffDir but I have this error where tStaffDir_PrevEmp is recognised as a column and not a table.
Pls advise how this can be done. Many Thanks.
View 3 Replies
View Related
Jan 30, 2007
I have a table wherein I have to update a particular column (receipt code) based on another column of the same table (receipt number). i have to do calculations in order to generate the correct receipt code, and i have to do this on every row of the table. how can i do this? will this update be in a loop or something?
View 6 Replies
View Related
May 13, 2014
I need to write a update statement which will update the same table with value from the same column but the values will be changing.
For an example.
The source table is as below.
ROWID DATA Header
1 AAA H1
2 isa1
3 isb2
4 AAA3b3 H2
5 same
Now the records with rowid 2 and 3 should be updated with H1 as Header which is present in the Rowid 1.
Similarly the record rowid 5 should be updated with H2 as header from rowid 4.
View 5 Replies
View Related
Jan 2, 2014
I am trying to remove a user from Reporting Services via code. I have found that the way to do this is to remove their details from an XML field in the table SecData
The name of the column is xml_description
An example value of the column is below:
<Policy>
<GroupUserName>BUILTINAdministrators</GroupUserName>
<GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId>
<Roles>
<Role>
<Name>Content Manager</Name>
[Code]....
I would like to remove the bolded part in the column when I put in the user's name.
So the code would start with:
Declare @username varchar (20)
Set @ username = 'EMEAMJohnson'
Followed by the logic to remove the bolded part of the column.
The column is called xml_description and the name of the table is SecData.
View 17 Replies
View Related
Apr 26, 2007
Hello all, was wondering if you could point me in the right direction for this.
I have a db with a column classifications, and in that column are 'Accountants - (1234)' 'Book-keepers - (18) etc etc. Now what I want to do is remove the - (xxxx) section and obviously the white space, so I am just left with 'Accountants' 'Book-keepers' etc.
1. Is there an easy way to do this?
Ok so my thoughts were create a another table and put the ammended classifications in that to update the first table. Your probably asking why not just do it on the first table...Answer. There are over 150,000 records to change.
So I now have my first table with column classification and my second table with the correct classification ammendments.
I can sit down and manually type
UPDATE table1
SET classifications = 'Accountants'
Where classifications = 'Accounts - (xxxx)'
until i have completed the entire list in table 2 but I was hoping you good people would know a way to work through the list so it would automatically update each classification correctly.
The problem I have here is I dont know how to work through table 2 and match it to something in table 1 and update it.
Any help is greatfully appreciated
View 16 Replies
View Related
Apr 11, 2008
Hi,
I am quite new in SSIS and I have a question. I don't know what is the best way to get it work...
So simply I have in my SQL Server database 2 tables :
First table "SER" fields
SER_ID
SER_IS_CHANGE_RELATED
where I have only the field SER_ID filled like
SER_ID SER_IS_CHANGE_RELATED
1234 NULL
1235 NULL
1236 NULL
Second table "SRE" fields
SRE_ID
SRE_SER_ID
SRE_CHA_ID
and the content would be for example
SRE_ID SRE_SER_ID SRE_CHA_ID
1 1234 2345
2 1234 2346
3 1236 2347
The 2 tables are related by the fields : SER_ID = SRE_SER_ID
So I would like to have the following result in my table "SER":
SER_ID SER_IS_CHANGE_RELATED
1234 True
1235 NULL
1236 True
"True" because one or more entry is present in table "SRE" for each SER_ID (= SRE_SER_ID)
Hope my example is clear....
Thanks for your help
View 3 Replies
View Related
Sep 6, 2007
How do I update a table's column, with the values of another tables column where the primary key between is Part_Number?
table2 = temp_table
Update Actuator_Components Set Scanned_Drawings = table2.Scanned_Drawings
View 2 Replies
View Related
Jun 8, 2005
This should be easy for someone, but I just can't seem to find a sample to do this.....I have created a table...CREATE TABLE dbo.test ( oId int NOT NULL UNIQUE, test1 varchar(50) NOT NULL PRIMARY KEY )Now, I need to go back and simply add another column to the table such as test2 varchar(50)Not sure if the insert is the way to go and been playing around with various statements but with no luck.Suggestions?Thanks all,Zath
View 2 Replies
View Related
Nov 17, 2006
Hifor MS SQL 2000/2005I am having a table (an old database, not mine) with char value for the column [localisation]Users[name] [nvarchar] (100) NOT NULL ,[localisation] [nvarchar] (100)NULLNow i have created a table [Localisation]Localisation[id_Localisation] [int] NOT NULL,[localisation] [nvarchar] (100) NOT NULLI am adding a new column to UsersALTER TABLE [Users] ADD [id_Localisation] int NULLand I want to update the Column [Users].[id_Localisation] before to drop the column [Users].[Localisation]something like UPDATE [Users] SET id_Localisation = (SELECT Localisation.id_LocalisationFROM Localisation FULL OUTER JOINUsers ON Localisation.Localisation = Users.Localisation)Users.Localisation can have a NULL value (then no id_localisation return)but it doesnt work because it returns > 1 rowthank youhow can I do it ?
View 10 Replies
View Related
Sep 25, 2014
I need to create trigger on table which will not allow to update value "1" into column and if tried to update.. then it should show error massage "Good To GO"
ID Name Roll
1 Ron 1
2 Jon 0
3 Nil 3
4 Par 1
if you try to update value "1" in Roll then it will through error
nil
View 4 Replies
View Related
May 9, 2008
I have a simple question that is more about performance on my code for updating a column with the sum of two columns:
Example
Table = dbo.adding
c1 c2 c3Sum
1 2
3 5
4 6
Now if I just simple write
Update dbo.adding
set c3SUM = c1 + c2
This works, but there is a null in c1 or c2 then I know I can write this:
if exists (select * from dbo.adding where c1 is NULL)
Update dbo.adding
set c1 = '0', c3SUM = '0' + c2 where c1 is NULL
if exists (select * from dbo.adding where c2 is NULL)
Update dbo.adding
set c2 = '0', equals = c1 + '0' where c2 is NULL
Update dbo.adding
set c3SUM = c1 + c2
This works as well, but I am not sure how perficient it is being that from my understanding the code will evaluate the table three times and update it based on the criteria. If the table consists of 100,000 rows then this might not be a good solution
Is there a more proficient way to do this... I personally try to as little coding as possible to help increase performance. I know I can make this very complex and declare variables and put everything in a loop, but I personally think that would be less profient..
View 8 Replies
View Related
May 6, 2007
Hi,
I have a set of records in database table and I want to update one column to be the same for all of them.
Can you suggest code solution?
View 1 Replies
View Related
Aug 30, 2000
My client want me to update a table and the view with a new column which should be hidden. I am kind of confused on how to go about this in order to get a good result. PLEASE HELP!!
View 1 Replies
View Related
Jun 21, 2014
my table payment_details structure is
payment_id payment_code
1 null
2 null
3 null
4 null
here payment_id is a primary key and i need to update the whole payment_id column to payment_code column.so i just tried the below query
update payment_details
set payment_code = payment_no
where payment_code is null
but it shows subquery error?
View 3 Replies
View Related
Oct 16, 2015
I'm trying to do the following:
update a tables value(a single column), based on a query of another database, using a common column.
Here's what I've cooked up so far:
Declare @trackingNo nvarchar (50)
Set @trackingNo =
(
select tracking_no from P21_Shipping.dbo.shipping_data t1
inner join P21.dbo.oe_hdr t2 on t1.order_no = t2.order_no
[Code] ...
print @trackingNoThe error it's returning is:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So, I'm wanting to query Shipping_data for order_No that matches the same orderNo column value in P21 Database(oe_hdr table), then update P21 oe_hdr table with "trackingNo from Shipping_data
View 8 Replies
View Related
Jun 10, 2008
HI ALL,
CAN ANYBODY HELP ME TO SOLVE THE ISSUE
I WANNA UPDATE A RECORD OF ONE TABLE WITH VALUES THAT ARE PRESENT IN A COLUMN OF ANOTHER TABLE
OR
CAN I DO THE SAME USING INSERT QUERY
THANKS IN ADVANCE
View 6 Replies
View Related
Nov 10, 2015
I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.
Txt_Temp - Table
I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR Add this code in viewÂ
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_CodeÂ
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber
Vin
123456
123123
123789
Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465
My Result can be in Txt_Temp table or new table or with one or two columns
asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789
View 10 Replies
View Related
Apr 6, 2007
Dear friends, can anyone please guide me on how to update table with results from derived column tool?
thanks,
View 9 Replies
View Related
Mar 17, 2008
In SQL 2005, I've created a test scenario in AdventureWorks where I:
1.) Create a database master key:
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'
GO
2.) Create a certificate:
CREATE CERTIFICATE HRCert
WITH SUBJECT = 'Comments'
GO
3.) Create a symmetric key:
CREATE SYMMETRIC KEY CommentKey
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HRCert
GO
4.) Add a test column to the HumanResources.JobCandidate table:
ALTER TABLE HumanResources.JobCandidate
ADD Comments varbinary(8000)
GO
5.) Open the symmetric key for use:
OPEN SYMMETRIC KEY CommentKey
DECRYPTION BY CERTIFICATE HRCert
GO
6.) Insert and Encrypt values ('Yes') to the newly created column:
UPDATE HumanResources.JobCandidate
SET Comments = EncryptByKey(Key_GUID('CommentKey'), 'Yes')
GO
-------
Great. Now all the textbooks say that, to view the column values in a decrypted state, you should:
SELECT CONVERT(varchar, DecryptByKey(Comments)) AS [Decrypted Comments], *
FROM HumanResources.JobCandidate
------
Great, I get it. But here's the rub. What is the best way to permanently decrypt the column and keep it in a cleartext state?
Running the following works, but keeps the column values in a varbinary (hexadecimal) state which is what we originally created:
UPDATE HumanResources.JobCandidate
SET Comments = DecryptByKey(Comments)
GO
But if I want to transform this varbinary(8000) column into a human-readable varchar column, the only thing I could come up with was a temp table solution:
UPDATE HumanResources.JobCandidate
SET Comments = DecryptByKey(Comments)
GO
DECLARE @temp varchar(1000)
SET @temp = (SELECT TOP 1 CommentsFROM Human Resources.JobCandidate)
CREATE TABLE #decrypt (Comment varchar(1000))
INSERT INTO #decrypt (Comments) VALUES (@temp)
GO
ALTER TABLE HumanResources.JobCandidate
ALTER COLUMN Comments varchar(1000)
GO
UPDATE HumanResources.JobCandidate
SET Comments = (SELECT Comments FROM #decrypt)
GO
DROP TABLE #decrypt
GO
--------
It works, but seems so inelegant to me.
Is there an easier way?
View 5 Replies
View Related
Feb 4, 2006
I am building a data warehouse. I have many columns I want to populate in a fact table using integration services. Sample fields are: companyID, companyName, companyNumberOfAccounts, company, NumberOfUsers.
In the integration services package, I would like to keep this fact table in place and populated with data and to build integration services packages that update each of the existing row's specific columns (e.g. companyNameOfAccounts) one by one. For example, I have a source of data that has companyID and companyNumberOfAccounts data.
Is it possible to use the SQL Server Destination or OLE DB Destination Integration Services elements to import companyID and companyNumberOfAccounts data so that existing records just have their companyNumberOfAccounts column updated?
View 1 Replies
View Related
Jun 26, 2006
Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to
show the user's pictures in perfect order (1,2,3,4,5,6...).
The Jist of my problem... When a user first inserts six pictures, he gets:
|1|
|2|
|3|
|4|
|5|
|6|
All is good. But, say he deletes picture |3|. Now the list order looks like this:
|1|
|2|
<- |3| is removed
|4|
|5|
|6|
And, then he inserts two more pictures, now he his this:
|1|
|2|
|4|
|5|
|6|
|7| <- |7| & |8| are added
|8|
What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.
I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:
Code:
ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)
AS
BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
BEGIN
SET @photo_number = 1
SELECT
@photo_number = (
SELECT COUNT(*)
FROM dbo.PersonalPhotos b
WHERE
a.photo_date < b.photo_date
)
FROM
dbo.PersonalPhotos a
ORDER BY
a.photo_date
END
BEGIN
My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:
photo_number photo_date
|1| 2006-06-26 21:43:36.653
|2| 2006-06-26 21:43:50.000
|3| 2006-06-26 21:45:25.217
|4| 2006-06-26 21:45:33.763
|5| 2006-06-26 22:39:42.670
|6| 2006-06-26 22:39:49.200
If |3| is removed above, the numbers are reordered based on the time of entry sequence.
Any suggestions on how to acheive this in my stored procedure? Currenly, i get the correct order, but it goes crazy when i delete and add.
Thanks and sorry for the verbose post.
View 5 Replies
View Related
May 29, 2014
I am writing a query to update table variable. It is throwing me some error.
I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table
update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID
View 9 Replies
View Related
