SQL Server Behaviour

Question

Post reply

SQL Server Behaviour

jj011185

Old Hand

Points: 324
More actions
August 11, 2008 at 6:03 pm

#196949

Lets say i have this sample data
Table A
rowid key desc
1 1 desc 1
Table B
rowid key desc
1 1 desc 2
2 1 desc 3
3 1 NULL
4 1 desc 5
now what i want to do is update Table A with what i have in Table B
Update Table A
Set
desc = isnull(Table B.desc, Table A.desc)
where
Table A.key = Table B.key
then, my question is how will SQL engine treat this kind of update
will the SQL engine go through each record and update then per rowid?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jj011185 Old Hand Points: 324 More actions · Answer 1

never mind found the answer,

SQL behaviour would be...

First Non Null Field for Each group of Record would be updated to the source...

so with the tables above this would mean, only

Table B

rowid key desc

1 1 desc 2

would be updated to Table A

but if you order Table B on descending order

this would mean that

Table B

rowid key desc

4 1 desc 5

would be applied

but in the case if

Table B

rowid key desc

4 1 NULL

this would be applied to Table A, assuming RowID 3 still is NULL,

Table B

rowid key desc

2 1 desc 3

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 2

SQL Server will search all rows that meet the WHERE clause, with matches and perform the update.

jj011185 Old Hand Points: 324 More actions · Answer 3

yes, but in case it has multiple result, it will yield to the first non null column, so even col1 could be updated from row 1 in results matching to where clause or col 2 in row 3 and col 3 in row 2, as long as its a non null field. it will do an aggregate behavior.