select & update: same cond but different # of rows

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

SQL Server 2000 Forums

SQL Server Development (2000)

select & update: same cond but different # of rows

Author

Topic

Cornelius19
Starting Member

30 Posts

Posted - 2008-08-12 : 15:04:36

Hi,

I have a select query and an update query, with the same conditions:

SELECT *
FROM FR_BAS_V
INNER JOIN func_bas ON
func_bas.baseform = FR_BAS_V.baseform AND
func_bas.bfpos = FR_BAS_V.bfpos AND
func_bas.sense = FR_BAS_V.sense

UPDATE FR_BAS_V
SET FR_BAS_V.f1 = func_bas.bfpos+func_bas.funcfeat
FROM FR_BAS_V, func_bas
WHERE func_bas.baseform = FR_BAS_V.baseform
AND func_bas.bfpos = FR_BAS_V.bfpos
AND func_bas.sense = FR_BAS_V.sense

Curiously, the first affects 216 rows while the second only 190 rows. How to explain this difference? I had a look on the updated data and there seems to be 216 rows affected (contrary to the update query respons).

Cornelius

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 16:55:56

See this explanation

-- Prepare sample data
DECLARE	@Source TABLE (i INT, j INT)

INSERT	@Source
SELECT	1,  1 UNION ALL
SELECT	1,  4 UNION ALL
SELECT	1, 40 UNION ALL
SELECT	1, 99 UNION ALL
SELECT	2,  2

DECLARE	@Target TABLE (i INT, j INT)

INSERT	@Target
SELECT	1, 3 UNION ALL
SELECT	2, 3

SELECT		*
FROM		@Target AS t
INNER JOIN	@Source AS s ON s.i = t.i

SELECT	@@ROWCOUNT AS [Rows Affected With Select]

SELECT	'Before update' AS [@Target table],
	*
FROM	@Target

UPDATE		t
SET		t.j = s.j
FROM		@Target AS t
INNER JOIN	@Source AS s ON s.i = t.i

SELECT	@@ROWCOUNT AS [Rows Affected With Update]

SELECT	'After update' AS [@Target table],
	*
FROM	@Target

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources