.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
sivanagamahesh
Post New Web Links

Update a column using CTE

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server
 
Hello, I am trying to update a field in a table based on calculation from other fields. I did it looping the records one-by-one using loops (or cursors) but it is taking time and it looked inefficient. Can anyone suggest better solution using grouping functions? I would like to stored the creditamt2 field for a groupid and maximum classid and put it separately (call RS1). then for a groupid if the checkkey belongs to 22, I would like to mutiply the creditamt1 fields till I reach lowest rank (A in this case) and then check the result (RS2) against (RS1). If RS1 > RS2, update the flag as 'credited', RS1 < RS2, 'debited' or RS1=RS2 'not changed' declare @insurance table ( groupid int, classid varchar(20), creditamt1 varchar(20), creditamt2 varchar(20), checkkey char(2), insuraceflag varchar(30) ) insert into @insurance select 100, 'A', 1.005, 1.25, 20, ' union all select 100, 'B', 1.023, 1.213, 22, ' union all select 100, 'C', 1.25, 1.32, 20, ' union all select 100, 'D', 1.5, 1.1, 20, ' union all select 101, 'A', 0.932, 1.2, 20, ' union all select 101, 'B', 0.99, 1.57, 20, ' union all select 101, 'C', 1.05, 0.738, 20, ' select * from @insurance --consider the classid A as min and Z as maximum record. output: for groupid 100, RS2 = 1.100, RS1 = 1.500 * 1.250 * 1.005 = 1.884 RS1 > RS2, credited for groupid 101, RS2 = 0.738, RS1


View Complete Post


More Related Resource Links

wss2.0 update/delete/hide lookup column that does not display any values

  

Hi All,

I have a document library that contains a Category column that is a lookup field. This is a default column that is a required field when uploading documents to the document library. The Category column is empty and I am unable to amend, hide, make it not required or delete it.

I have gone to Modify settings and columns -> clicked on the Category field to edit, but there is no option to amend the content or delete it. I am only able to amend the Column name and Description.

Since then, I have amended the column name to eg. Category1 and created a new Category field as a lookup and linked it to the correct list.

The problem I am facing now, is that I cannot hide, delete or make the Category1 (old Category) field NOT required. Either I would like to update the original field to display the correct values or alternately hide, delete or make the column not required.

Please help.


Copy a column with update and select

  
Hi, I am using SQL Compact 3.5 I am trying to copy the values of a column called "CODIGO" from one table to another table. I mean, I want to copy the column "CODIGO"  from table  "DATOS_ACADEMICOS" to table "ALUMNOS". I use this code: UPDATE ALUMNOS  SET CODIGO=(SELECT DATOS_ACADEMICOS.CODIGO FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   But I get an error in the SELECT statment: Major error 0x80040E14, Minor error 25501 [ Token line number = 2,Token line offset = 13,Token in error = SELECT ]   I also tried this code with the same error: UPDATE ALUMNOS  SET CODIGO = (SELECT DATOS_ACADEMICOS.CODIGO             FROM DATOS_ACADEMICOS             WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID) WHERE EXISTS (SELECT 1 FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   How can I copy from one column to another in a SQL sentence? Thanks    

conditional update a column base on multiple result

  
Hi, I have a requirement that to conditional update a column base on the table result within a single update statement.     declare @TableStageStatus table ([OldTable] [varchar](100) NULL, [OldKey] [varchar](100) NULL, [StatusCode] [varchar](10) NULL) declare @Table table ([Key] [varchar](100) NULL, [row_stus_cd] [varchar](10) NULL) INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','I') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','I') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','E') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','I') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'2','E') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','E') INSERT INTO @Table ([Key],[row_stus_cd]) VALUES ('1',null) INSERT INTO @Table ([Key],[row_stus_cd]) VALUES ('2',null) UPDATE A SET A.row_stus_cd = case when row_stus_cd is null then B.StatusCode when B.StatusCode='E' then 'E' else B.StatusCode end FROM @Table A INNER JOIN @TableStageStatus B ON A.[Key]=B.OldKey and B.OldTable='a' WHERE B.StatusCode in ('E','I') select * from @Tab

Update nullable column in db to null?

  
Can anyone reliably get the EDS to save a nullable column to the databse as a "null" when bound to any of the controls such as "FormView"? I have tried using several different UpdateParameters (Session, ControlParamater, Parameter,  etc). I have tried setting "ConvertEmptyStringToNull" to true and leaving the property off entirely. Nothing works. On my "Inserts" it works fine. (I have made sure the column is set to nullable = true in the Entity Designer.....)

update stored procedure for existing values of column(salperyr)

  
tbl_salary salary           salperyr   hike20      hike20yr10000.0000    NULL      12000.00    144000.0012000.0000    NULL      14400.00    172800.0014000.0000    NULL      16800.00    201600.0015000.0000    NULL      18000.00    216000.0018000.0000    NULL      21600.00    259200.0020000.0000    NULL      24000.00    288000.0022000.0000    NULL      26400.00    316800.00in above table salaryper yr (salperyr) has to be modified after caliculation my null values has to be removed and place (salary*12)in one shot.so plz suggest me update stored procedure for this.

update stored procedure for existing values of column(

  
tbl_salary salary            salperyr    hike20       hike20yr 10000.0000    NULL      12000.00    144000.00 12000.0000    NULL      14400.00    172800.00 14000.0000    NULL      16800.00    201600.00 15000.0000    NULL      18000.00    216000.00 18000.0000    NULL      21600.00    259200.00 20000.0000    NULL      24000.00    288000.00 22000.0000    NULL      26400.00    316800.00 in above table salaryper yr (salperyr ) has to be modified after caliculation my null values has to be removed and place (salary*12 )in one shot.   so plz suggest me update stored procedure for this

Update one column of a table with data from another table

  
I have two tables which are different with the exception of a single field (column name = "LocationCode"). Both tables can also be joined with a common id field. I would like to update the "LocationCode" values in one table with the the "LocationCode" of the other table. I can do a relational join on the common id. It is impractical for me to update one row at a time. How can I update all the rows quickly?   But one more point both tables lies in different database Varinder Sandhu http://varindersandhus.blogspot.com/

Update one column of a table with data from another table

  

I have two tables which are different with the exception of a single field (column name = "LocationCode").

Both tables can also be joined with a common id field.

I would like to update the "LocationCode" values in one table with the the "LocationCode" of the other table. I can do a relational join on the common id.

It is impractical for me to update one row at a time. How can I update all the rows quickly?

 

But one more point both tables lies in different database


Varinder Sandhu http://varindersandhus.blogspot.com/

how to update a column with some rules in it (base on other columns)

  

Hello Sir,

i have table named mytable1 it has values as below,  with composite primarykey (CKCOL1,CKCOL2,CKCOL3)
how to update finalresult values as below math, i tried it with different groupings

CKCOL1 CKCOL2, CKCOL3, SalesID,   Team1,    Team2,     Team3,     counts,   units,      ordertotal,     otheramount,  FinalResult
DDN 175 ETQ NULL 10:40 MON JUN28 NULL 1 0 2814 NULL 
DDN 175 ETQ NULL 10:40 TUE JUN29 NULL 1 0 4938 NULL
DDN 175 ETQ NULL 10:40 MON JUL05 NULL 1 0 2814 NULL
DDN 175 ETQ 31566 10:40 NULL JUL13 9 NULL 13998 3432 NULL

DDN 175 TVQ NULL 1:40 SUN JUL25 NULL 2 0 7831 NULL
DDN 175 TVQ NULL 1:40 SAT JUL24 NULL 2 0 2168 NULL
DDN 175 TVQ 40546160 1:40 MON JUN1 3 NULL 5000 NULL NULL
DDN 175 TVQ 40546245 1:40 TUE AUG1 1 NULL

Update 1 column in a table from another table

  

Sorry for the simple question.

 

Scenerio:

 

Table 1                                                                                         Table 2

Client ID       BankID       BankContact                                            BankID            BankContact

1                  1                                  &

Adding a new column in update query

  

Hi all,

 

Is it possible to add a new column in an update query? I am joing two tables  with [Encountered Month] from Table 1 and [Calendar Date] from table 2 and trying to add one column[Fiscal Month] into the first table where  dbo.table1.[Encountered Month]=dbo.table2.[Calendar Date]

Appreciate your help.


Update Multiple Row in same column with different text

  

Hi all!

I am trying to explain with example what i want to do. I have a table that has names and surnames of people. Some of names are null in table and i know its number. I want to update null values but i want to give to every row different names not updating with single name.

ali yilmaz
NULL mert
NULL pol

Can i update these two records with names of 'ahmet' and 'yasir' within one single update statement

I hope i could explain my question. Any help would be so appreciated.

Thanks...


Ahmet UZUN

computed column to update it with max(value)+1

  

HI ,

  i have a column match_id up till now i have inserted values in it,now i have to make it a computed column with formula on it.

   now i am not going to insert the value for match_id column ,insted of that when i insert a row in that table then value of match_id column should be max(match_id)+1 .

foe eg.

a  b   match_id

1 2  1200

4 3  1201

8 9  1202

now  when i iniser a=12 and b=56 then match_id should calculated as max(match_id)+1 i.e

1202+1=1203 ,i had already  used identity column so tell me how to achive it???

Thank's

Digambar 

 


How to manually update entity object in .edmx to reflect a new column in the DB?

  

Is there a way to manually update a single edmx entity to reflect a new column in a SQL db table? If yes, what would be the proper step- by-step procedure?  

I prefer not to use the "update model from db" template because this will update all the tables and wipe out all the customizations and name changes I have already made in the EF objects.  This seems a bit overkill for a single column change.   I went down this route once, and I prefer not to do it again.   I do not see any way to actually update a single table from the DB using VS2010 "update from db", but please enlighten me if this is not correct.  

What I have tried to do so far is manually add a new scalar property to the appropriate entity using the edmx designer, and this seems to work OK, but I cannot seem to find a way to map the new property back to the db through the mapping window.  Do I have to do this directly through the XML file?   Also is this sufficient to ensure the new column is correctly reflected in the edmx, or are there other steps that need to be done? 

Sorry if this has been covered before, but I could not find an exact match to my issue.

Advise on the best way to proceed would be greatly 

Update statement with CASE inconsistently updates VARCHAR column, stripping leading zeroes.

  

I have an update statement below that strips the leading zeroes off the front of AU. This value will have typically '0001234' or something like this. In my case statement, I have 2 scenarios: 1) when I use the commented out phrase (case when 1=1), the statement leaves AU alone as it should when the condition is met. 2) When I use the other statement, it STRIPS the leading zeroes off AU - I don't want it. I am just telling it to set it as is. Why is this doing this and what can I do to stop it. I was just attempting to set AU = AU in these conditions.

BEGIN TRANSACTION
SELECT * from msdn.MSDN_Import1
UPDATE msdn.MSDN_Import1 SET MSDN.MSDN_Import1.AU =
--CASE WHEN 1=1                                                                       -- when I use this line, AU is untouched and stays AU = '0001234'
CASE WHEN NullIf(MSDN.MSDN_Import1.LOBCC,'') IS NULL            -- when I use this line, AU='0001234' becomes AU = '

Need help with an Update statement, trying to update a 'bit' type column.

  

I have a function that checks for a username and if it finds the username it will allow the user to update the user, there are 3 main columns: Name,Age,isFemale.  I have the enduser supply the information and then I try to update the record but I get a sql error for "not a valid" column.

console app:

 Console.WriteLine("Please enter your fullname");
                        string spName = Console.ReadLine();
                        Console.WriteLine("Please enter you age, only use digits");
                         int spAge = int.Parse(Console.ReadLine());
                        Console.WriteLine("Are you a female?, 1 for Yes - 0 for No");
                        int spIsFemale = int.Parse(Console.ReadLine());
                        Person sp = new Person();
                        sp.name = spName;
                        sp.age = spAge;
                        
                        if (spIsFemale == 1)
                            sp.Gender = Gender.Female;
                        else
                            sp.Gender = Gender.Male;

                        Console.WriteLine(sp.savePerson());


I pass the spIsFemale by using an enum, here is the Person Class with function.

 public enum Gender { Female, Male };

class Pe

Update column with some logic

  

Hello,

Can you please help how I can update table #case_p2 to get the required results.
I need to update colum-Con_id of #case_p2 from column-Con_id of #case_p1.
I dont want to give IF condition in the update script because there are so many records and if condition will not be possible. Is any other logic to update table-#case_p2 to get required output.
Can we use ROW_NUMBER() over function in this script.

Please note that column-Con_id of #case_p2 has three records of every number of column-Con_id of #case_p1.

create table #Case_P1
(Con_id int, Con_vol int)
insert into #Case_P1 values (1,10000)
insert into #Case_P1 values (5,12000)
insert into #Case_P1 values (13,14500)
insert into #Case_P1 values (17,18500)

create table #Case_P2
(Con_id int, Con_vol int)
insert into #Case_P2 (Con_id) values (1)
insert into #Case_P2 (Con_id) values (1)
insert into #Case_P2 (Con_id) values (1)
insert into #Case_P2 (Con_id) values (13)
insert into #Case_P2 (Con_id) values (13)
insert into #Case_P2 (Con_id) values (13)
insert into #Case_P2 (Con_id) values (5)
insert into #Case_P2 (Con_id) values (5)
insert into #Case_P2 (Con_id) values (5)
insert into #Case_P2 (Con_id) values (17)
insert into #Case_P2 (Con_id) values (17)
insert into #Case_P2 (Con_id) values (1

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend