SQL Server 2012 :: Perform Update But Skip Lock

Mar 7, 2014

I open query analyser and on one tab I update a record in a transaction and hold it.

begin tran
update customers set territory = 'x' where customer = 'A00001'
--rollback tran

In a second tab I attempt to update all records in the table

update customers set carrier = ''

Clear this fails because of the lock placed during the first script and this is fine.

However, is there a way to get the 2nd script to ignore the locked rows and just update as many as it can? The obvious answer seemed to be the READPAST hint like follows…

update customers with (READPAST) set carrier = ''

…but this is still blocked by the original lock. I’ve tried combining it with all sorts of other table hints but all seem to get blocked.

The following does work, ignoring the lock and not returning the data

Select * from customers with (READPAST) where customer = 'A00001'

I’ve tried combining this with the update like so…

update customers
set carrier = ''
from customers with (READPAST)
where customer = 'A00001'

..but this is blocked too.

I’m so desperate I tried moving the update into a cursor and update one row at a time. Nothing worked. I thought I might be able to do something like this

If (Select count(*)
from customers with (READPAST)
where customer = 'A00001') > 0
--then perform update

..but this returns a value of 1 even though the following returns no rows.

Select * from customers with (READPAST) where customer = 'A00001'

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: How To Perform Update Query That Involves Multiple Tables

Aug 31, 2015

I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:

SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an

[Code] ....

I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?

The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.

View 9 Replies View Related

SQL Server 2012 :: Find Queries That Lock Tables Or Not Using Primary Key While Running Update

Jul 20, 2015

I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.

If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.

If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.

View 2 Replies View Related

How To Lock A Row When Perform SELECT Statement

Apr 17, 2004

Hi,
i would like to know how to lock a table from other to view when select statement..can anyone help me with that???
i tried


SELECT * FROM IMIS_FRAMEWORK_RUNNING_NUMBER with (HOLDLOCK,ROWLOCK)
WHERE running_type=@type;


but i m still able to see my row when i perform my SELECT STATEMENT from other query analyser..

Please help me with this..thank you

View 6 Replies View Related

SQL 2012 :: Bulk Insert - How To Skip First Row

Oct 31, 2014

I used code below to do bulk insert. Since csv file first row is column name. How to skip first row?

BULK
INSERT TEST
FROM 'c: est.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO

View 4 Replies View Related

SQL 2012 :: Command To Uninstall Node With Skip Validation?

Jun 25, 2015

what is the command to uninstall sql node with skip validation in sql 2012 ?

View 1 Replies View Related

Better Way To Perform An Update? (T-SQL)

Jun 6, 2008

Hi all, I hope you guys can help me with the following bit of T-SQL. I already have a solution but I really don't like it and I've been trying to find a simpler more elegant way of doing the same thing.

Firstly, let me present you with a brief explanation of what I am trying to do together with some sample data for you to play with and hopefully assist me in finding a better solution than the one I’ve come up with.

So.. here goes

I have two tables:

create table #VehMake (VehMakeId int, VehMake varchar(100))

insert into #VehMake
select 222, 'FORD' union all
select 210, 'FORD (USA)' union all
select 223, 'FORD (AUS)' union all
select 269, 'HONDA' union all
select 253, 'NISSAN' union all
select 280, 'VOLKSWAGEN'


This contains various vehicle makes which I'm sure you'll recognise!

The second table contains vehicle histories:


create table #VehicleHistory (PersonId int, VehMakeVehModel varchar(200), VehMake varchar(100), VehModel varchar(100))

insert into #VehicleHistory (PersonId, VehMakeVehModel)
select 1, 'FORD (USA) MUSTANG' union all
select 2, 'HONDA CIVIC' union all
select 3, 'NISAAN ALMERA' union all
select 4, 'VOLKSWAGEN PASSAT'


As you can see, in the second table, the second column contains a string of the vehicle Make and Model in one string. What I need to do is to split the Make and Model in to separate columns with an update statement.

This seems easy enough with a simple LIKE comparison:


VehMakeVehModel like VehMake+' %'


....BUT if you notice, there are two records in the #VehMake table that are similar but not the same. These are the 'FORD (USA)' and 'FORD (AUS)'. The update statement would return two records from the #VehMake table when trying to match with the first record in my #VehicleHistory table.

As I said, I did come up with a solution but it seems over complicated and I have a feeling that there is a way of doing this with an update. Maybe use the LEN() function but I'm not sure.

Your help would be much appreciated.

BTW, once I've identified the correct Make, I can easily populate my model as all I have to do is use the replace function on VehMakeVehModel column and remove the matched make to get the full model name.

Hope that makes sense and thanks for any help in advance.

View 8 Replies View Related

How To Perform An Update To A Collumn With Two Substrings

Mar 22, 2005

I want to do the following in an update:

Update [table]
Set collumnName = Substring(collumnName, 1, 2) = '11' AND Substring(collumnName, 3, 5) = '00000'
Where (conditional statement)

SQL is not letting me perform the update because of the AND statement between the two Substring's. Is there anyway around this...is there anything else I can do to acomplish what I'm trying to do.

Thanks in advance everyone.

RB

View 5 Replies View Related

The Fastest Way To Perform An Update ... Advice Needed :)

Nov 12, 2006

Hi all,

I have a situation where my Visual C# application presents a number of fields. In order to update a student object, I wish to call a stored proc. 1 or more fields can be updated... And If one is left null, then I don't want to update it, but instead I want to keep the old value.

I am really wondering if I am approaching this the right way. The following stored proc does what I want it to do, however I'm thinking there may be a faster way...

Here it is:

-- Update a student, by ID.

DROP PROCEDURE p_UpdateStudent

CREATE PROCEDURE p_UpdateStudent

@ID INT,

@NewFName VARCHAR(25),

@NewOName VARCHAR(25),

@NewLName VARCHAR(25),

@NewDOB DATETIME,

@NewENumber VARCHAR(10),

@NewContactAID INT,

@NewContactBID INT

AS

BEGIN

SET NOCOUNT ON;

-- DECLARE THE OLD VALUES

DECLARE @FName AS VARCHAR(25)

DECLARE @OName AS VARCHAR(25)

DECLARE @LName AS VARCHAR(25)

DECLARE @DOB AS DATETIME

DECLARE @ENumber AS VARCHAR(10)

DECLARE @ContactAID AS INT

DECLARE @ContactBID AS INT

-- Get all of the old values

SELECT @FName = FName FROM TBL_Student WHERE ID = 10000

SELECT @OName = OName FROM TBL_Student WHERE ID = 10000

SELECT @LName = LName FROM TBL_Student WHERE ID = 10000

SELECT @DOB = DOB FROM TBL_Student WHERE ID = 10000

SELECT @ENumber = ENumber FROM TBL_Student WHERE ID = 10000

SELECT @ContactAID = ContactAID FROM TBL_Student WHERE ID = 10000

SELECT @ContactBID = ContactBID FROM TBL_Student WHERE ID = 10000



-- USE ISNULL to set all of the new parameters to the provided values only if they are not null

-- Keep the old ones otherwise.

SET @NewFName = ISNULL(@NewFName, @FName)

SET @NewOName = ISNULL(@NewOName, @OName)

SET @NewLName = ISNULL(@NewLName, @LName)

SET @NewDOB = ISNULL(@NewDOB, @DOB)

SET @NewENumber = ISNULL(@NewENumber, @ENumber)

SET @NewContactAID = ISNULL(@NewContactAID, @ContactAID)

SET @NewContactBID = ISNULL(@NewContactBID, @ContactBID)

-- Do the update

UPDATE TBL_Student

SET FName = @NewFName,

OName = @NewOName,

LName = @NewLName,

DOB = @NewDOB,

ENumber = @NewENumber,

ContactAID = @NewContactAID,

ContactBID = @NewContactBID

WHERE

ID = @ID

END

GO

So yeah it works. But As you can see I wish to keep an old copy of the values to perform checks pre update....

Is there any faster way, or am I on the right track? I need a pro's advice :) (before i write all of my procs!!)

Thanks all.



Chris





View 7 Replies View Related

Passing Parameter To OLE DB Command To Perform Update On Paradox Table

Aug 23, 2007



Dear All -
iam facing a problem for passing a parameter to update paradox table statment in the OLE DB Command

Briefly an OLE DB Command exist between OLE DB Source and OLE DB destination , all i want it to send the data that flow from the OLE DB Source to the update paradox table statment in OLE DB Command ,

i use the parameter annotation which is "?" to refer to the parameter in the update where clause and it sucess when i use the OLE DB Command to connect to SQL Server DB but when i use it with the Paradox Table an error appear saying that there is more than one data source column with the name "?" The data source column names must be unique.

i dont know what that means is it means that "?" it consider "?" as a column or what or is there another way to send parameter to Update statment for the paradox table differ from the SQL Server Update statment which is the "? "

i hope someone help me as soon as possible because i need it in work sOOon
Thanks,
Maylo

View 2 Replies View Related

SQL 2012 :: No Disks Were Found On Which To Perform Cluster Validation Tests

Sep 9, 2014

I am getting following errors in my Cluster Validation report when trying to create a Windows Cluster.

I have 2 nodes DB01 and DB02 . Each has 1 public ip, 1 private ip (for heartbeat), 2 private ips for SAN1 and SAN2. The private ip's to SAN are directly connected via Network Adaptor in DB01 and DB02.

Validate Microsoft MPIO-based disks
Description: Validate that disks that use Microsoft Multipath I/O (MPIO) have been configured correctly.
Start: 9/9/2014 1:57:52 PM.
No disks were found on which to perform cluster validation tests.
Stop: 9/9/2014 1:57:53 PM.

[Code] ...

View 9 Replies View Related

Update - Not Releasing The Lock After Update

Sep 16, 1999

Pls. help me,

How can I kill the LOCK after update is completed in the table?

My application is complaining that other user still using the system.


This is a part of my trigger to do un update on CallLog table

.......
begin transaction
Update Heat.CallLog
set ModTime=@Vancovertime Where CallID=@strCallID
Commit Transaction

View 1 Replies View Related

Update Lock

Jun 23, 2008

can anybody explain following query.


SELECT * FROM TABLE1 e WITH (UPDLOCK)
JOIN TABLE@ i ON Id=Id

View 2 Replies View Related

How To Lock The Row Or Table When Update?

Nov 4, 2004

Hi,
I am working on a project which need to produce a sequential certificate number, Everytime I need a new certificate number, I need to find out what is the max number in the database and then the new certificate number just max+1.
But how can I block another
transaction to check what is max certi. number while this transaction is in the middle of writing the new certificate number(max+1) into database . Does ADLockOptimistic work in this case? Here is the code:
My database sql 2000.

cmdTemp.CommandText="Select max(certificateNumber) from product_table where certificateNumber<> 8888888"
set cert_info=Server.CreateObject("ADODB.RecordSet")
cert_info.Open cmdTemp, , AdOpenKeySet, adLockOptimistic
If Not cert_info.EOF then

certnumber=cert_info(0)+1

End if
cert_info.close
set cert_info=nothing

View 10 Replies View Related

Lock Database To Update

Feb 21, 2006

Hi,I need to lock a database (prevent users from connecting) in order toupdate it. I already know how to kick everyone out with their spid buti can't figure how to prevent them from reconnecting.Thanks !

View 5 Replies View Related

SQL 2012 :: Finding Lock Escalation Cause

Jun 17, 2015

While running SQL Server Profiler I reached some Lock:Escalations. When I searched for Statements having same SPID as Lock:Escalation event I realized that one of delete statements causes this. Is there any way to find out why lock escalation in such place occurres?

Statement is like:

delete from BOOK_IN_LIBRARY where libraryId in (,,,,); <-20 elements ids

CREATE TABLE BOOK_IN_LIBRARY(
[libraryId] [bigint] NOT NULL,
[bookId] [bigint] NULL,
[otherData] [bigint]NULL,
[otherData2] [int] NULL,

[Code] ....

View 0 Replies View Related

Insert And Update With Trigger Causing Lock

Feb 20, 2008

Hi,
I'm taking an Excel spreadsheet (that could have around 30k rows) and processing it in SSIS. I essentially have a flag in one of the spreadsheet cols that indicates whether the record is already in the database or not.

I'm splitting the data using a conditional split on this column and using a OLE DB Destination (Fast Load) to perform the inserts and a OLE DB Command to fire a stored procedure to perform any updates. Both the OLE DB Destination and the stored procedure are hitting the same table and the two operations could be executing at the same time as they both appear directly after the Conditional Split, so the OLE DB Destination is set NOT to lock the table.

This seemed to work OK until recently. I've just added 2 triggers onto the table in question which I don't want to fire 30,000 times during the import. As the OLE DB Destination is set to use Fast Load, it doesn't fire the triggers - cool. In the update stored procedure it disables the trigger before performing it's update and re-enables the trigger when finished. Currently this does mean that if you only had updates, the trigger could be enables/disabled 30,000 times. That sounds kinda bad, but I don't really know if this carries a large overhead or not?

If, when importing now you have both updates and inserts the whole process locks up. From looking at activity monitor, it seems as though the INSERT gets suspended.

Do I have a fundamental problem with how I've structured the Data Flow or am I just being really stupid in Enabling/Disabling a trigger that many times, which is probably causing the problem?

Thanks in advance.

View 3 Replies View Related

SQL 2012 :: Lock Escalation On Create Procedure?

Feb 3, 2015

When I run the SQL Profiler to detect lock escalation, I noticed it detected a lot of lock escalation with the textdata indicating "create procedure" on certain stored procedure.

View 9 Replies View Related

SQL 2012 :: Export Dead Lock Info To User?

Dec 10, 2014

How to export the dead lock information to the user ?

View 2 Replies View Related

SQL Server 2012 :: Update Statement Will Not Update Data Beyond 7 Million Plus Rows Out Of 38 Millions Rows

Dec 12, 2014

I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).

SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

View 5 Replies View Related

SQL 2012 :: Failed To Lock Variable RunID For Access With Error

Sep 17, 2015

I have created some packages in visual studio and run the packages from there and they all run fine. I have then imported the THEM in SQL Integration services Catalogues and run the packages individually and they all run successfully.When I tried to create the job to run the packages in a scheduled job I get the following error messages: Backup database error: failed to lock variable "RunID" for access with error 0xc001001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the variable collection on a container during execution of the package and the variable is not there.....

Backup database error. The execute method on the task. Backup database returned code 0x80131904 (Login failed for user 'loginname' The execute method must succeed and indicate the result using an "out" parameter.

View 0 Replies View Related

Conditional Split For Insert Or Update Cause Dead Lock On Database Level

Aug 28, 2007

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

View 7 Replies View Related

SQL 2012 :: Error 1222 - Lock Request Time Out Period Exceeded

Mar 21, 2013

What is this? How do I fix it? I get a ton of these and it slows down the server for 5 minutes.

An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.

View 1 Replies View Related

Update Statement Performing Table Lock Even Though Where Condition On Clustered Primary Index?

Jul 20, 2005

Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray

View 1 Replies View Related

SQL 2012 :: SSMS Object Explorer Lock Timeout When Running Compression Script

Jan 21, 2015

ALTER TABLE [dbo].[TableNameExample] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)

The table is 110 GB, so will take time to compress. But it is one table in a database with 60 tables. Why would executing this statement, cause lock timeout on Object Explorer in SQL Server Management Studio? Users cannot drill down objects in this database without getting lock timeout.

When I cancel the compression script, users of SSMS can access objects in this database again from the GUI. Why does compressing a specific table affect access to all tables metadata? I cannot find anything on the internet but I am sure this has happened to other people.

View 3 Replies View Related

Replication Performance Degrade In Unidirectional Direction And Lock Time Out (Update Are High Than Inserts)

Feb 21, 2007

 

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1)   One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

 

on subscriber database  response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

 

looking for any experieced solution/suggestion.

Thanks in advance. 

View 3 Replies View Related

SQL Server 2012 :: Update ID In The Table?

Oct 11, 2014

I wrote query to update id int(1,1) to some value but its not giving me the result. Is it not possible to update id on the table.

View 7 Replies View Related

Error: A Deadlock Was Detected While Trying To Lock Variable X For Read Access. A Lock Could Not Be Acquired After 16 Attempts

Feb 2, 2007

I simply made my script task (or any other task) fail

In my package error handler i have a Exec SQL task - for Stored Proc

SP statement is set in following expression (works fine in design time):

"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"

From progress:

Error: The Script returned a failure result.
Task SCR REIL Data failed

OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.

Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

And how did I get 4 errors? - I only set my script task result to failure

View 11 Replies View Related

SQL Server 2012 :: Sub-Query For Update Statement

Feb 14, 2014

I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.

SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' +
Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727))
+ ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID)
From Parts As P
Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
Order By categoryMy results:

Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead

View 5 Replies View Related

SQL Server 2012 :: How To Update Fields Having Same Names And DOB

Apr 11, 2014

Create Table #Temp (ID int not null primary Key, Name varchar(25) not null, DOB datetime not null, Sex char(1), Race char(1), Height int, Weight int)

insert #Temp
select 1, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', NULL,NULL, NULL, NULL
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223

[Code] .....

/* Expecting Output */

select 1 as ID, 'Kenneth' as Name, '1963-02-26 00:00:00.000' as DOB, 'M' as Sex,'C' as Race, 516 as Height, 160 as Weight
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223
union

[Code] .....

View 5 Replies View Related

SQL Server 2012 :: Detect Update Statistics

May 20, 2014

I am working on an existing infrastructure and i do not have liberty to change much right now. I am in a situation where app issues update statistics command quite often. So frequently that sometimes one blocks another. Is there any way i can do something like this

IF ( update_statistics going on)
dont do anything
else
run update statistics

This is temporary solution untill i fix bad inline SQL code (in app) and use SPs.

View 8 Replies View Related

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 :: Update Record In Table?

Nov 7, 2014

To see where is the problem I am trying to count rows in the database.First I create a table A with 2 columns namely tablename, rowbefore and rowafter and I insert records in it as below.

INSERT INTO A
SELECT TableName = o.name, '', Rows = max(i.rows) FROM sysobjects o
INNER JOIN sysindexes i ON o.id = i.id
WHERE xtype = 'u' AND OBJECTPROPERTY(o.id,N'IsUserTable') = 1
GROUP BY o.name
ORDER BY o.name
Then I update rowbefore with rowafter as below.
UPDATE A SET rowbefore = rowafter

Now I launch my application with update records in the database.Then I am trying to update rowafter with new records as below.

UPDATE A
SET rowafter = (SELECT max(sysindexes.rows) FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE xtype = 'u' AND OBJECTPROPERTY(sysobjects.id,N'IsUserTable') = 1 AND A.tablename = sysobjects.name)

Does this update really update my column rowafter or not?

View 2 Replies View Related







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