Clarification in update statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Clarification in update statement

Author

Topic

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-11 : 10:24:03

Hi experts,

Table1
id empno empname
1 e1 a
2 e2 b

update table1 set empname='c' where id=1

If i execute the above query, only empname column gets updated in table or all the columns are updated by their old value??

What happens in DB engine?

I Think entire row(all the column) get updated !

My assumption is correct? else advice me..

Weather this behavior is applicable for all the DB's like oracle,mysql etc??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-11 : 10:43:43

Hey Plz help me yar!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-11 : 10:49:42

People on this forum are not paid, we don't wait anxiously at our keyboards for questions. For example, I was about to answer your question a few minutes ago, but had some of my own work to do.
Now I have come back to your question.

The value of 'a' in them empname column will be changed to 'c' because that is where the id = 1. It will change to 'c' wherever id = 1. Your other questions don't make a lot of sense.

Jim

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 11:30:38

hmmm. I think the question asked is:

)Is there a difference in performance between UPDATE 1 column in 1 row of a table compared to UPDATE all columns in 1 row of a table.

I thought that there would be but I'm not so sure...

Following code makes up some sample data (random strings) and then updates half of them. The first UPDATE does only one column, the second updates all the columns (and transposes two of them).

On a dataset of 45190 rows the IO stats / execution plans / and time in batch are exactly the same for the two UPDATES


IF OBJECT_ID('tempDB..#foo') IS NOT NULL DROP TABLE #foo

CREATE TABLE #foo (
	[Id] INT IDENTITY (1,1)
	, [valA] NVARCHAR(32)
	, [valB] NVARCHAR(32)
	, [valC] NVARCHAR(32)
	, [valD] NVARCHAR(32)
	, [valE] NVARCHAR(32)
	
	)

-- Sample Data for #foo (random data)
INSERT #foo (
	[valA]
	, [valB]
	, [valC]
	, [valD]
	, [valE]
	)
SELECT
	CAST(CAST(NEWID() AS VARBINARY(32)) AS VARCHAR(32))
	, CAST(CAST(NEWID() AS VARBINARY(32)) AS VARCHAR(32))
	, CAST(CAST(NEWID() AS VARBINARY(32)) AS VARCHAR(32))
	, CAST(CAST(NEWID() AS VARBINARY(32)) AS VARCHAR(32))
	, CAST(CAST(NEWID() AS VARBINARY(32)) AS VARCHAR(32))
FROM
	information_schema.columns a
	CROSS JOIN (
		      SELECT 0 AS [seed]
		UNION SELECT 1
		UNION SELECT 2
		UNION SELECT 3
		UNION SELECT 4
		UNION SELECT 5
		UNION SELECT 6
		UNION SELECT 7
		UNION SELECT 8
		UNION SELECT 9
		)
		b

-- Objects in #foo
SELECT COUNT([Id]) FROM #foo


-- Test Suites
SET STATISTICS IO ON


	UPDATE #foo SET
		[valA] = 'FOOO'
	WHERE
		[Id] % 2 = 1

	UPDATE #foo SET
		[valA] = 'FOOO'
		, [valB] = 'BAR'
		, [valC] = 'WOO'
		, [valD] = [valE]
		, [valE] = [valD]
	WHERE
		[Id] % 2 = 1

Any thoughts?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-11 : 11:31:41

quote:
Originally posted by jimf

People on this forum are not paid, we don't wait anxiously at our keyboards for questions. For example, I was about to answer your question a few minutes ago, but had some of my own work to do.
Now I have come back to your question.

The value of 'a' in them empname column will be changed to 'c' because that is where the id = 1. It will change to 'c' wherever id = 1. Your other questions don't make a lot of sense.

Jim

Jim I think u did't catch my question!

When a update statement hits a DB the entire page of the DB will get updated ! rather then a single column.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 12:35:13

quote:
Originally posted by senthil_nagore

quote:
Originally posted by jimf

People on this forum are not paid, we don't wait anxiously at our keyboards for questions. For example, I was about to answer your question a few minutes ago, but had some of my own work to do.
Now I have come back to your question.

The value of 'a' in them empname column will be changed to 'c' because that is where the id = 1. It will change to 'c' wherever id = 1. Your other questions don't make a lot of sense.

Jim

Jim I think u did't catch my question!

When a update statement hits a DB the entire page of the DB will get updated ! rather then a single column.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

what do you mean by entire page of db?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-11 : 13:13:54

I think he is asking that if you say UPDATE column1, but there are 6 columns, Does it ONLY update column1 with its new value, or does it update ALL of the columns, meaning column1 gets its new value, but all the other columns get their OLD values.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources