Compare Two columns and update the Third Column


I have three columns and want to update the third column based on the comparison of first two columns in the same table.

For instance:

Col1 - val1, val2, val3, val4, val5......
Col2 - NULL, NULL, val1, NULL, val2....


Thanks for any help.

5/21/2008 8:50:38 AM 29906 articles. 0 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 33

When you define a column, you can define it as a "computed column".

Make col3 a computed column and fill in the comparison expression.


5/21/2008 8:55:31 AM

 Dear David,

Thanks for your reply. 

Actually I have table with more then 7000 records in it. and all the columns are already defined. I was looking for the solution which I can use now to update the third column. I was update the third column one by one by check and replace method.

I thought there might be a quick query to do so.


5/21/2008 10:48:04 AM

y can't you write like as ,

update tablename set col3 = col1,col2 some expression

are you want to do seperate manipulations for diiferent types of values?

by using where clause, you can do also 

update tablename set col3 = col1,col2 some expression  where col1 = conditino1 and col2 = condtion2 like that also you can write


you can write in another way, taking all the table values into a table variable and loop all the rows, for each row so the updation

eg:  create table @temp


               column1 int,

               column2 int,



  insert into @temp select col1,col2 from tablename

 while((select count(*) from @temp) > 0)


                //                     get the top col1 and col2 values,

                // with those values try to update the 3rd column in original table.

           // delete the top 1 record from @temp


Still you didn't get the way , post your tables definition and data and what you required 


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
5/21/2008 11:07:17 AM

 There's a simple way, and a not quite so simple way, and a hard and slow way.

Simple way:

Assumption 1:  To compute the value, only literals or other column values in same record are needed, possibly some function calls.

Assumption 2:  All col3 values must use the exact same formula. 

Modify your table in sql server management studio.  Go to the col3 entry.  Put in a formula into the computed column formula, such as (col 1 * col 2) + 5

Change the is persisted value to yes.

Save the changes.

You are now done.  Now and forever more.

Not quite so simple way:

Assumption 1: Only some col3 values should have the formula applied. 

update the_table
set col3 = (col1 * col2) + 5
where col4 = 'some_value'

Your data is now updated, as of when the_table was updated.  Of course, someone creating a new record or updating an existing record just broke things again.

Hard and Slow way:

Assumption 1: Your rules for what should be inside of col3 are so convoluted that your brain can't figure out a way to do this except to inspect each record one by one.

Assumption 2: There is a darn good reason to have such convoluted rules, instead of just simplifying them.

For this approach, you will need to create a cursor and loop thru the records one at a time.  Lots of examples of cursors online, read up on them and come back with specific questions.

And, of course, unless you embed this same logic into a trigger, as soon as someone inserts or updates a record, col3 is broken again.


5/21/2008 11:58:51 AM

Similar Artilces:

Addition of two Columns in third Column.
Hi Guys, I got stucked in my project and i need your help. I have a data grid with data from sql 2005. I have got two amount columns - Amount1 & Amount2 with values from database. I want the addition of both in Total column (type: template) which I added in data grid view I also want the grand total of Total column in the bottom. Pls help  >I have got two amount columns - Amount1 & Amount2 with values from databaseWhy not change the select? ChangeSELECT  Amount1, Amount2 FROM FREDto SELECT  Amount1, Amount2, (Amount1 + Amount2) AS Amount3 FROM FRED...

Multiply two columns and store the result into third column
Hi,  I am new to world. I am having 1 problem in a gridview.   There are 3 template fields in my gridview namely Length , Width and Area. Length n Width contain textboxes and Area is having label. Problem is as soon as you enter the values in textboxes, the area should be calculated and reflected immediately in the area field of that row. I am using RowDataBound event gridview. But area is not getting update dynamically after changing the textfields.  Code i m using is: protected void gvArea_RowDataBound(object sender, GridViewRowEventArgs ...

display the sum of two datagrid columns into third column
hi... there..     i have a datagrid with columns named Price ,Quantity and total. now in the total column i  wanna display the prices which comes by multiplyiing the price and quantity (like  total=Price * quantity). I'm workin in                     may i knw hw can i do this.. plz write the code.. thanks.         Hi, do like this..suppose u have,        string ProductPrice = ...

When is a column not a column?
I have several extremely similar datawindows that I have to maintain that go against the same table. Recently, I had to add a column to that table, and also add the column to ALL of these datawindows that go against this table. I added the column to the first datawindow, set up the edit control style, etc. I then did a copy/paste of the columns edit control to the remaining dw's (of course remembering to first add the column in SQL). Everything (update props, column specs, column props) seems to indicate that the pasted column is linked to the column I selected, HOWEVER, when I...

SQL query to one column od database into two columns
Hi,I have a empskill Table which has 3 Columns (1) EmpID (2) SkillTypeID and (3) CourseID.Now  SkillTypeID column has data 1 or 2 here 1 means Primary Skill and 2 means Secondary Skill. CourseID will reflect the ID's of subjects like c#,SQL,etc I need a Query which will count the number of primary skilled and number of secondary skilled persons based on subject and will display as followsCOURSE ID      SKILL TYPE  21                        222  &n...

to Calculated Columns, then to update another column in same table in ADO.NET
to get several Columns, then pass those Columns into  one function which will return  a result,then using the result to update another column in same table in ADO.NET, How can I do this? thanks. Hi   what ever information you given is not clear. so please give me  detail info.Ganesan  Clarity is required. Plz explain clearly wts u r requirement.Thank uBaba Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. ...

How to subtract two numeric columns(bound) of a Datagrid and display the result in the third column?
Hi Everyone,I  have two numeric columns(databound-coming from SQL database) in my datagrid, 'PromoPrice' & 'CostPrice'. I need to calculate 'PromoPrice- CostPrice' and display the result in the third datagrid column. i.e.: column2-column1 = column3Any help anyone...Many Thanks in advance,Nico. In your query do this:Select .... , (PromoPrice - CostPrice) AS Answer, .....FROM yourTableKeep in mind you can do math functions in queries.DarmarkMark as Answer, if this reply answers your post. Hi Darmark, thanks for your fast answer.The fact is I'm using a single "select function"...

How to subtract two date columns(bound) of a Datagrid and display the result in the third column?
Hi Everyone,I  have two date columns(databound-coming from SQL database) in my datagrid, 'start date' & 'end date'. I need to calculate number of days with 'end date- start date' and display the result in the third datagrid column. i.e.: column2-column1 = column3Any help anyone?...ASAP please got a deadline to meet :) (SOS!!!)Many Thanks in advance,Arty.Aartee...we live more than once! Hi, there are several ways of doing it. One of them is creating a label in the TemplateColumn, use the Helper function to calculate the number of days.<ItemTemplate>   <...

Comparing two columns in two tables
How do I compare, via SQL, the contents of two, or more, columns in two different tables? The table structures are the same (one is a baseline delivery, the other the current table), but the current table will have more records. I would like to periodically monitor the activity in the table, but don't have auditing and can't modify triggers... ASE 12.0 and Unix Thanks What types of comparision do you want to do? difference in number of rows? values that appear in one but not the other? average value? standard deviation? -bret Jim wrote: > How do I com...

Modified columns: at update() used all columns instead the key and updated ones
Hi all, I have a datawindow set up as "key and modified columns" as updates, I will retrieve 1 row data from DB (SQL Server 2008, snc 10), then I modify 2 columns and do Update(). At update, the 'where' statement is issued with ALL columns, but I was expecting it will have only the key ('id') and the two columns I updated. I am using PB11.5, target is a windows executable. The project was migrated from PB10.5. I tried to issue ResetUpdate() after retrieve(), but still got same behaviour. Please can someone give me a check-list to let me digg if er...

Afterstatment update(column) not detecting a change in value if the column is modified in a Before Update trigger.
Hi, I am currently using version: I am not sure if this issue is by design or may infact be a bug . If someone could clarify this it would be greatly appreciated. for the purpose of narrowing down this issue I have made a simple test case scenario. What happens is, if stPaid is changed by the before update trigger the if .... update(stPaid) condition in the after statement trigger does not return that stPaid has been changed (even though it actually was). In addition if I dont allow the before statement to change stPaid and I change it via my software or a...

UPDATE column in table1 from 2 columns...
Hey ya'll... I need to create a query that I can run that will allow me to essentially take the first initial and last name and combine them from two columns in one table and update one column with that new data in another table. Example of need: UPDATE tblLogin.UserName with (first character(tblEmployee.FName)+(tblEmployee.LName)) WHERE tblLogin.EmployeeID = tblEmployee.EmployeeID. That was TOTALLY pseudo code and I know I'll need a join statement in there somewhere. Both tables reside on the same database, such as this: dbEMPLOYEE (Database)--> tblLogin (Table)----> UserName (Col...

Compare values between two columns in two different databases
I'm trying to compare a value from one column (artistPlaylist) to a value of another column (artistCD) in another database. The first column is in a database called "Playlist". This database is being updated every 5 minutes or so and when the page is loaded it always shows the last 5 records entered. The second column is in a database called "Merchandise" which holds all the CD's I have available. What I would like to do is that when the page loads, it looks at the 5 most recent records of the "Playlist" database and compares the "artistPlaylist" c...

Updating a Column in SQL
I am trying to update a users status from Pending to either Approved or Rejected.  I created the following handers to update me db by I keep getting a syntax error. What am I doing wrong? public partial class admin_beta : System.Web.UI.Page{    protected void ApproveButton_Click(object sender, EventArgs e)    {        SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI");        SqlC...

Web resources about - Compare Two columns and update the Third Column -

Compare the Meerkat - Wikipedia, the free encyclopedia
Compare the Meerkat is an advertising campaign on British commercial television for , a price comparison website, part of ...

INFOGRAPHIC: How Does Facebook’s Social Jobs Partnership App Compare With LinkedIn?
How does the Social Jobs Partnership application Facebook launched with several partners last November stack up when compared with LinkedIn ? ...

Speak English - Listen, Repeat, Compare on the App Store on iTunes
Get Speak English - Listen, Repeat, Compare on the App Store. See screenshots and ratings, and read customer reviews.

A$AP Rocky compares Danny Brown to ODB - Back & Forth - Episode 1 - Part 2/5 - YouTube
Like This? You Should Subscribe Here Now: Watch Part 3 Next: In part two of 'Back & Forth' A$AP ...

Bieber compares minor Ferrari crash to Princess Diana's death
New York Daily News Bieber compares minor Ferrari crash to Princess Diana's death The aftermath of a crash involving Justin Bieber's ...

How Microsoft Surface tablets compare in a crowded market
... Monday, Steve Ballmer and the other Microsoft execs referred to the company's upcoming Surface devices as tablets. So it makes sense to compare ...

Tool that compares your income shows most Australians are out of touch
... of touch when it comes to understanding wealth in Australia. The interactive calculator asks readers to guess how their household income compared ...

Kanye West compares Kim Kardashian to a dinosaur
Kanye West decided to pursue Kim Kardashian after being inspired by a Will Ferrell movie.

NSWRL compares proposed changes to Super League war
NSWRL boss Dave Trodden has compared the NRL's proposed pathways changes to that of the infamous Super League competition.

Resources last updated: 12/15/2015 12:06:20 AM