SQL Server 2012 :: Update A Column Using Value Of Another Column

Sep 9, 2015

I have a student table like this studentid, schoolID, previousschoolid, gradelevel.

I would like to load this table every day from student system.

During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.

My question in my merge statement something like below

Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid

When matched then update

set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;

My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?

View 4 Replies


ADVERTISEMENT

SQL Server 2012 :: How To Update One Column Value To Another In Same Table

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

SQL Server 2012 :: Drop And Update Column?

Feb 16, 2015

I am looking for standard sql code for below 2 concern.

1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx

2-I want to update all the table where table name start with ArchiveBbx

example:-

Update table Archivebbxfbcc
set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))

View 3 Replies View Related

SQL Server 2012 :: Table Variable Update Column

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

SQL Server 2012 :: Update Column In Stored Procedure

Mar 30, 2015

In a t-sql 2012 stored procedure, I would like to know how I can complete the task I am listing below:

In an existing t-sql 2012 stored procedure, there is a table called 'Atrn' that is truncated every night. The Table 'Atrn' has a column called 'ABS' that is populated with incorrect data.

The goal is to place the correct value into 'ABS' column that is located in the Atrn table while the t-sql 2012 stored procedure is excuting.

**Note: The goal is to fix the problem now since it is a production problem. The entire stored procedure that updates the 'dbo.Atrn' table will be rewritten in the near future.

My plan is to:

1. create a temp table called '#Atrnwork' that will contain the columns called,
Atrnworkid int, and ABSvalue with a double value.

2. The value in the column called Atrnworkid in the '#Atrnwork' table, will obtain its value from the key of the 'Atrn' called atrnid by doing a select into. At the same time, the value for ABSvalue will be obtained by running some sql when the select into occurs?

3. The main table called 'Atrn' will be changed with a update statement that looks something like:

Update Atrn
set ABS = ABSvalue
join Atrn.atrnid = #Atrnwork.Atrnworkid

In all can you tell me what a good solutiion is to solve this problem and/or display some sql on how to solve the problem listed above?

View 5 Replies View Related

SQL Server 2012 :: Update Temp Table Where The Column Names Are Unknown

Dec 26, 2013

In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.

So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:

UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))

However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.

how can I get the column names of a Temp table dynamically while doing an Update?

View 4 Replies View Related

SQL Server 2012 :: How To Update Master Count Column From Multi Thread Application

Sep 28, 2015

We have an application that runs Jobs, each of which affect ## number of child objects (usually around 1M). When a thread gets to 5000 updated child objects it bulk inserts into a table called ActionLog with the child Id and JobId.

When the job is complete a sproc SUMs the children from the ActionLog table:
select sum(id) from ACTIONLOG where JOBID = @JobId;

It then updates the Jobs table AffectedObjectCount column with the sum(*) from above.

Instead of writing to the ActionLog table and calculating the SUM at the end I would like to do this 'real time'. After the bulk insert I would like to update the AffectedObjectCount column with the number of rows that were just bulk inserted. I tried this in the past and ran into major contention issues. There are usually 20 threads running a job so there exists a lot of potential for deadlocks.

Is there a recommended way to handle updating one column on one row from multiple threads? What is the best practice for a counter like this?

View 0 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

SQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?

Sep 18, 2014

A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.

I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.

The text before ;# (35106) should be inserted in to another column of the same table.

Below is the table structure:

create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)

View 2 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

Jul 25, 2015

I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.

The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

Below is the schema of the three tables

I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.

DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'

[code].....

View 2 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

SQL Server 2012 :: Update Table From Variable Table Column?

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

SQL 2012 :: Trigger After UPDATE INSERT Based On Column Value

Jul 18, 2014

I would like to change a value after the table updates inserts a new row based on the value of that column

Table Name is: MPanel
Col Names: RID, FPID.

I want to:

If column RID='16'
Then UPDATE FPID='1'

How can I trigger that update? Would this work:???

CREATE TRIGGER dbo.MyTrigLI
ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
UPDATE [MPanel]
SET
FPID='1'
WHERE (UPDATE(RID='16')
END

View 9 Replies View Related

SQL 2012 :: Get Identity Column Value When Update Statement Fails

Sep 9, 2015

My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.

However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.

Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.

Now, I was asked to log the specific record where the failure was occured.

Something like log the identity column value or primary key value which record has failed.

View 4 Replies View Related

SQL 2012 :: Update A Column With A Sequence Of Numbers Starting From 1?

Oct 7, 2015

If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;

Obviously... The stuff inside the IF is wrong syntax...I mean

UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;

View 4 Replies View Related

SQL 2012 :: Single Query To Update Same Column Name In Different Tables With Where Condition?

Sep 25, 2014

I have two tables table1 and table2 and having a common column "col1"

When i ran the following query

UPDATE table1, table2 SET col1=FALSE WHERE id = 1;

getting the following error

Error Code: 1052 Column 'col1' in field list is ambiguous

id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.

how to achieve this in single query?

View 4 Replies View Related

Update Column Value In Whole Database (based On Column Value)?

Aug 27, 2015

How to Update Column Value in the whole data base (based on Column Value)?

View 2 Replies View Related

Convert Column And Update Column

Jun 20, 2000

Hello,

I'm using SQL Server 7. I have an invoice table. The invoice table has a datetime column called InvoiceDate. The InvoiceDate column contains the following date format:
5/3/00
I would like to use the InvoiceDate column to update the char (6) column called zInvoiceDate as a formatted date field like yymmdd.

The following syntax did not work:
SET zInvoiceDate = Convert([ARInvoiceID],GetDate()12)

Any suggestions please :)
Thanks,
Denise

View 4 Replies View Related

Update Column Value Based On Value In Another Column?

Jan 29, 2008

Hi.

I'm writing a web application with VS2005 andSQL Server 2005 express edition.

I have an SQL table:

Table name:
statistics

Columns:
stat_id
firm_name
stat_month
stat_year
view_count
follow_count
percentage

When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:

UPDATE [statistics]
SET percentage = follow_count / view_count * 100
WHERE (stat_id = 15)

This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)

Any help would be appreciated.

View 2 Replies View Related

Update 1 Column With Data From Another Column

Mar 12, 2007

Hello,

I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help.
What I want to do is give a column in a table the same value as another column from the same table. For example:
Table:Requests
A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.

View 6 Replies View Related

SQL 2012 :: Why Does Server Consider A Value To Be A Column Name

Jul 22, 2015

This should be a really simple, straight forward query, but apparently it's not. The error I get is "Invalid column name 'A'. I've put it in quotes and not in quotes and I left spaces around the equal sign and I've eliminated them. What's wrong with the SELECT statement below?

SELECT * from firms WHERE firm_id= 100002
AND active_firm = 1 AND firm_id != 100092
AND trust_id = A;

View 6 Replies View Related

SQL Server 2012 :: Need To Select A Column Without 0

Jun 21, 2014

I have a table like below

create table #temp
(
valid_id int not null,
valid_no varchar(50)
)

10001
20002
30011
40012

i need to select the valid no without '0' lie 1,2,11,12, how do i?

View 7 Replies View Related

SQL Server 2012 :: Get Row Value As Column Header

Dec 21, 2014

I have two tables namely lu_parameter and tbl_param_values. The lu_parameter table consists of param_id and parameter column containing id numbers for the parameter names.

UIDParam_ID Parameter Param_Threshold
1P1 Parameter1 NULL
4P2 Parameter2 NULL
6P3 Parameter3 NULL
7P4 Parameter4 NULL
8P5 Parameter5 NULL
9P6 Parameter6 NULL

The tbl_param_values table consists of values corresponding to the parameters with the param_id value as the column header.

UIDSiteIDP1P2P3P4P5P6P7P8
1721177503107NULLNULLNULL
27227129537710NULLNULLNULL
372897125851113NULLNULLNULL
4726881252997NULLNULLNULL
5727867502612NULLNULLNULL
673071312567107NULLNULLNULL
77214127535911NULLNULLNULL

I want to join these two tables so that in the result query instead of param_id value as column heading, I need to have the parameter value as the column heading.

UIDSiteIDParameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6 Parameter7 Parameter8

17211
27227
37289
47268
57278
67307
77214

I have attached the screenshot of the data for reference. I am not that much aware of sql queries.

[URL] .....

View 9 Replies View Related

SQL Server 2012 :: Select Value Then Must Put In Column

May 6, 2015

I am trying to select leveling; I have table like this;

FACC_WID FACC_BKEY FACC_CODE FACC_DESC FACC_CODE_DESC FACC_DESC_CODE FACC_H1_L5_CODE FACC_H1_L5_DESC FACC_H1_L5_CODE_DESC FACC_H1_L5_DESC_CODE FACC_H1_L4_CODE FACC_H1_L4_DESC FACC_H1_L4_CODE_DESC FACC_H1_L4_DESC_CODE FACC_H1_L3_CODE

[Code] ...

Now I need to make 5 levels like this:
1
10
100
1001
100100

When column FACC_CODE have 1 char then its a level one so I put this in FACC_H1_L1_CODE..

I this possible in (SSIS)?

View 1 Replies View Related

SQL Server 2012 :: How To Get A Sum Of One Set Of Data In Same Column

Sep 6, 2015

I put on the site excell file where I explained what I want to get . It is easier to explain through an example .

THIS IS MY INPUT DATA:

ACCOUNTS CODE ACCOUNTS NAME TOTAL

0 CLASS 0
011 Synthetic group
011-1301 Some name 1 25 $
011-1401 Some name 2 20 $
022 Synthetic group
022-1011 Some name 1 101 $

[code]...

View 9 Replies View Related

SQL Server 2008 :: How To Update Certain Column From All Tables Within DB

Mar 19, 2015

I have a query, I am trying to update a certain column in my query you can see that is hard coded. The column that I am trying to update is "O_Test" I used a select statement trying to figure out how many records that accounts for with the entire database of all the tables. I got 643 records. So I am wondering if there is a way I can update all the columns without looking up each table and updating each one. This Update statement wont work because I am accounting for all records in the DB of all tables associated of what I hard coded

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%O_Test%'
ORDER BY schema_name, table_name;

View 5 Replies View Related

SQL Server 2008 :: Update Binary Column

Mar 25, 2015

The last two columns in one table is [StarText](varchar(20)) and [Star] (binary). It stored data like below:

StarTest---Star
***
**
Null
*****

How to write a update code to insert star image at column [Star]?

For example, at column [Star]
row1 insert 3 stars
row2 insert 2 stars
row3 keep null
row4 insert 5 stars

View 2 Replies View Related

SQL Server 2014 :: Update Duplicates In A Column

Aug 13, 2015

How do I append an accumulating number to duplicates in a column? I would prefer to update the table

for example

Name
John
John
Tom
Bob
Bob
Bob
Mike

to look like

Name
John
John1
Tom
Bob
Bob1
Bob2
Mike

View 1 Replies View Related

SQL Server 2008 :: Processes That Update A Certain Column

Oct 26, 2015

I have a table and a specific column inside this table. I know this table is being updated, by using sys.dm_db_index_usage_stats, I was able to determine this, by some process (stored procedure / SQL Job / etc), but the problem is, I am not sure what process is doing it.

How would I search our SQL Server 2008 database to find any process that manipulates this table / column (I only care about Inserts / Updates and Deletes, but do not really care for SELECT).

View 8 Replies View Related

SQL Server 2012 :: DateAdd From Max DateValue Column

Jan 8, 2014

I am trying to generate a @StartDate and @EndDate. The @StartDate would be the 1st of January and the year is dependent on the @EndDate.

The @EndDate would be
select max(DateValue) from dbo.testtableThe column DateValue in the testtable contains dates such as 2013-12-09, 2013-12-15.

What I am trying to accomplish is if the @EndDate is populated by select max(DateValue) from dbo.testtable returns '2013-12-15' as the max(DateValue). I want the @StartDate to '2013-01-01' because the year of the EndDate is 2013.

So something along the lines of
@StartDate = DateAdd(yy,?,@EndDate)

View 2 Replies View Related

SQL Server 2012 :: Group By Column As Sub Header

Jan 28, 2014

I have a table which I would like to format like so:

From this:

CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200) NULL,
CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED

[Code] ....

Just as a single varchar(max) field.

View 9 Replies View Related

SQL Server 2012 :: More Than 8000 Chars In A Column

Jan 30, 2014

Is there any possibility to store more then 8000 chars in a column?

View 1 Replies View Related







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