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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

what is wrong with the Following Update Statement

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :Sql Server

I have a Select that is defined like this



 select C.Number,A.GrpName from TBL_CNTC C
    ON A.CntcID = C.ID
    where A.GrpName <> C.Number


and it give me records that dont match in the the field "GrpName" and "Number"  and now i want to Update one table from the other like this



                UPDATE TBL_CNTC 
		SET Number = A.GrpName
		ON A.CntcID = C.ID
		where A.GrpName <> C.Number

Now as you can see above i  i want to Update the table TBL_CNTC from the table TBL_ACTV and it looks like like it updated data

(167 row(s) affected)


View Complete Post

More Related Resource Links

Whats wrong with this statement


Im trying to updated some old ASP pages with some new .net pages and cant seem to get this update statement to work. I need to update the current ASP code below and then once thats working im going to use that same statement in a procedure for all our new .Net pages..

So can anyone see what im doing wrong by inserting the 2nd column to the update statement?

strSqual = "update tas set date_completed='" & TransDate & ", trans_id=" & InsertedTransID & "' where date_completed is null and sercontract_id="& strContractID & " and DATEDIFF(dd,task_due_date,'" & strProcDate & "')<=14"


I am trying to add an extra column to the update, but not sure if the syntax is correct.

update statement which i can use in execute sql task.

I am having a table in the following manner.I am having  yearmonth till 202012 and from 201005 to 202012 I am having activeflag,tablename1,tablename2,tablename3,tablename4 as zero. This table needs to be update before runing the package.Before I load data for tablename1 for 201005 I need to update that 201005 for tablename1 to 1 and activeflag to 1 and  I need to update the 201002 tablename1 to zero.and when I load data for 201006 I need to update first that active flag =1 for 201006 and tablename1 to 1 for 201006 and then Update 201003 tablename1 to zero.i.e in tablename1 at a given time i need to see only 3 (yearmonth)=1 and update the first non zero record to zero.similary i need to update other tables also.  yearmonth                      Activeflag           tablename 1       tablename2         tablename 3   tablename4 201001                             0                 &nb

Update Statement help from Multiple database

Hi I am having two separate datbase on same sever one datasbase name private from where i am executing my below query and other name is ODS Table name in Private=[A].[DimCapabilitySpecialty] Table name in ODS = [A].[CapabilitySpecialty]   both of them having column SpecialtyCd   i want to update the flag value of ODS table where there is particular specialitycd missing in source means table in private database   so for that i wrote below query   update A.DimCapabilitySpecialty set ActiveInd ='I' where SpecialtyCd !=[ODS].[A].[CapabilitySpecialty].[SpecialtyCd]   But it is giving me this error   Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "ODS.CapabilitySpecialty.SpecialtyCd" could not be bound.

Update statement not updating records and returns no errors

I am running an Update statement against an SQL database on server 2008. The update runs but does not update the record, but returns no errors. The database is part of a commercial help desk package called TrackIt 8.5 which was just a fresh install on a new web server and a new sql server. The update is running from a web form we use to automatically create active directory accounts. After the account is created the web form closes out the work order in the Trackit db. This was working fine when the system was running on Server 2003 and SQL server 2005. Running Trackit version 7.02. The login use to access the database has full permissions to read and write to the database, just as it did on the old server which never had an issue updating. Odd thing is, if I log onto the SQL server and make a change to any single field in that record. I can then run the web form and it updates the record normally. All other update statements that are running on other databases are running fine. Anyone have any suggestions on what to check that may be causing this issue?

Update query from select statement


Cana you I need to do an update from a select statement...i'm really close here but have not quite figured out the nuance...

the full select statement provides correct results and i've joined the outer table with the inside table....can you make recommendations?

BEGIN TRAN --rollback

UPDATE geprs_price..T_ITEM_PRC_CHG 


		FROM geprs_price..T_ITEM_PRC_CHG gep
		and gep.prc_eff_dt = dm.prc_eff_dt
		LEFT JOIN construct..T_AD_USERS con
		ON dm.CONFIRM_BY  = con.UID
		LEFT JOIN construct..T_AD_USERS crt
		ON dm.CREATE_BY  = crt.UID

		AND dm.PRC_TYP_ID in (39)



Cumulative Update 5 - wrong SQL 2008 version number


Hi all,

I installed Service Pack 1 for SQL 2008 on one of my servers. This server has multiple instances. In this case, it is the instance "SHAREPOINT2010" that is giving me difficulties.

For Sharepoint 2010, we need to install cumulative update 5. This update fails due to the fact I do not have the correct SQL version. It is expecting version 10.0.2531 (SP1), but it says I have the 10.0.1600 (RTM). When I check in my management studio and via the @@version, I do see the confirmation I have version 10.0.2531. (also see attached screenshot).

Does anyone know why the cumulative update fails to see the correct server version ? If I try to install it on another SQL 2008 SP1, it works fine..;

Thank you !



screenshot sql version

CLR Timeout when running an update statement


I have created several CLR'S for several webservices but i had no need to update my tables until now, most of my CLR'S return data to the cusstomer but now i have a CLR that receives information from a webservice and updates my data, When running a Select statement within the CLR everything is fine but when I send and update statement the CLR times out without good explanation. the code below is what Im using and that will reproduce the problem.

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    <System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert, Unrestricted:=True)> _

Public Shared Sub sp_Request_TransNetwork()

        Using conn As New SqlConnection("context connection=true")

                       ssql = "select fields from my table" --this works perrfect!!!


Use XML DML with a select or update statement.

Given an SQL XML table column with contents like this:

  <taxcert>A18 3865 10</taxcert>
  <exempt_reason />

I'm trying to modify it with an SQL query like this:

        [XMLData].modify('insert <entityusecode_id />
              after (/fld)[1]

This SQL causes this error:

Msg 8137, Level 16, State 1, Line 39
Incorrect use of the XML
data type method 'modify'. A non-mutator method is expected in this context.

Deadlock: Store Procedure that only contains select statement owns an update key lock


I have a seemingly simple deadlock graph that contains 2 SP's. One SP is updating a table, while another SP (the victim) is selecting from it. The interesting thing about the graph is that the SP that contains the select (and only a select) is shown to own an UPDATE lock on the table that SP2 wants to update. How is this possible?



OPENQUERY Update statement errors


Right now, I am struggling with OPENQuery on multiple fronts.  a) Not sure if the stored proc code below is correct b) Getting multiple syntax and other errors with the code and its a high time for me to resolve this on an urgent basis.


Here's what I get when I run the following Create Stroed Proc command:


Server: Msg 7321, Level 16, State 2, Procedure sp_GroundSure_UpdateSentOrFailedRequest_new, Line 25
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00936: missing expression
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].


Any help will be helpful in this regard.


CREATE PROCEDURE sp_UpdateSentOrFailedRequest_new



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.

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 = '

what's wrong in my if - else statement?


I have a problem making a couple of if - else statements work. This is what my buttonclick does: It displays ID, last name and firstname of every customer in the database that are registered with the street that is entered in the textbox in question. This works. What doesn't work is when nothing is entered into the textbox (error message label is not showing) or when a street that doesn't exist in the database is entered (error message label is not showing). Can anyone find what's wrong with my code (c#)? Thankful for help!

protected void ButtonSearch_Click(object sender, EventArgs e) 
        LabelNoStreet.Visible = false;
        LabelNoCustomerChosen.Visible = false;
        string Street = TextBoxStreet.Text;

        if (Street != null) 
            SqlConnection conn = new SqlConnection(config);

              string sql = "SELECT ID, firstname, lastname, street FROM Customer WHERE street='" + Street + "'";
              SqlCommand comm = new SqlCommand(sql, conn);
              SqlDataReader dr = comm.ExecuteReader();

                  if(Street == Convert.ToString("street")) 

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;
                            sp.Gender = Gender.Male;


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

 public enum Gender { Female, Male };

class Pe

update fomr OLEDB command goes wrong


Hello all,

I use a conditinal split to check if records exists and have changed. Is so the dataflow goes to a storedprocedure to update the table. The stranges thing happens, all the records got updated with the same change, so for instance all the birthdates of users are the same after the update. It seems like the update commande holds the same values for the variables, when i look with a dataviewer I see different data then the data inserted. Anyone a clue what this can be?

within the oldb command I cal the stored procedure as "exec spUpdateall ?,?,?,?,?,?,?,?,?,?,?,?,?,?"
Where the questionmarks are the ammount of variables.

Thank you


Trouble with an update statement


I'm 100% sure this is going to be something really simple but I'm using a dataset to update a table which works fine until I ask it to update using data from a textbox.

So this won't work:

Dim updateCMS As New AdminCMSTableAdapters.AT_CMSTableAdapter
updateCMS.Update("Home", "Home", txtHome.Text, 1)

But this will:

Dim updateCMS As New AdminCMSTableAdapters.AT_CMSTableAdapter
updateCMS.Update("Home", "Home", "Test", 1)

Any ideas because its driving me insane?!

Thanks in advance.

Primary key violation on update statement for sqlserver2005 in asp.net


Violation of PRIMARY KEY constraint 'PK_StudentInf'. Cannot insert duplicate key in object 'dbo.StudentInf'.
The statement has been terminated.

the above message is appearing when i am upadating the record.

I am doing project  using asp.net 3.5 c#.net and sqlserver 2005,

i am executing sp for update using sqlhelper block, when i am updating record the above error occuring rarely(not for all records), if i update one record if get an error above second time it is updating nicely,

i am not able to finding the problem when and where it is occuring.

one thing here i am upadating primary column also like hallticket NO.

is there any problem if update primary column .

please tell me the solution it's urgent, and also it is on LIVE.

please give me solution as early as possible.

Using EXEC/EXECUTE with an UPDATE T-SQL statement - not firing as expected


I'm working on EXECUTING a simple UPDATE statement for a configuration table.  The table contains one row of configuration data in multiple columns with different data types.  In order to simplify adding new columns to different configuration tables, I'm making a get and set procedure that determines where the setting is stored and sets it in the applicable table.  Determining the applicable table is easy, but setting the value is not working as expected.  The SQL that is generated below executes perfectly in SSMS, but when running the following SQL, EXEC does not execute the generated SQL as expected.

    SampleName VARCHAR(50),
    SampleNumber INT,
    SampleDate DATETIME


    @SQL VARCHAR(1000),
    @ConfigColumnName VARCHAR(50),
    @ConfigValue VARCHAR(7950),
    @ReturnValue INT

SET @ConfigColumnName = 'SampleName'
SET @ConfigValue = 'Test'

SET @ConfigValue = REPLACE(@ConfigValue, '''', '''''') --change single quotes to pairs of single quotes in value
SET @SQL = 'UPDATE #ConfigTest SET [' + @ConfigColumnName + '] = ''' +

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