strange selection for update

Question

strange selection for update

joerg.kannengiesser

Old Hand

Points: 322
More actions
October 30, 2024 at 9:43 am

#4475325
Hi,
yesterday I had to update several rows of a table.
The rows for update where selected by a select-statement for testing the results;
the selection for the update-statement selected this sub-selection but I missed that I renamed the primary key within the selection;
normaly I would think that SQL should raise an error (because field not in selection) but the result was that ALL (!) records were updated !
example:
select for testing the results:
```
select * from (select PKey as myKey, WERTA from TempSel where PKey like'3%') as Q1
```
update with error (no unique field in subselection):
```
update TempSel set WertA = 'TEST' where PKey in 
(select * from (select PKey as myKey, WERTA from TempSel where PKey like'3%') as Q1)
```
update ok (57 rows):
```
update TempSel set WertA = 'TEST' where PKey in 
(select myKey from (select PKey as myKey , WERTA from TempSel where PKey like'3%') as Q1)
```
update ok (57 rows) :
```
update TempSel set WertA = 'TEST' where PKey in 
(select * from (select PKey as myKey from TempSel where PKey like'3%') as Q1)
```
update NOT ok (all 691 rows !!!): did not see the renamed field, but why is there no error raised (field Pkey not in subselection !)
```
update TempSel set WertA = 'TEST' where PKey in 
(select PKey from (select PKey as myKey from TempSel where PKey like'3%') as Q1)
```

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

While I don't know the answer to your question, my question is why are you writing your update like this? Why not this?

UPDATE TempSel
SET WertA = 'TEST'
WHERE PKey LIKE '3%';

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Since you aren't qualifying a single column as to who owns it, the optimizer will try to figure out what you're doing. I believe, in the end, that last query, decided that you wanted to match all of TempSel where PKey IN PKey, because it couldn't pull PKey from anywhere else... but I would have thought you'd see an error instead. Qualify the object owners all the time in your queries.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

joerg.kannengiesser Old Hand Points: 322 More actions · Answer 3

your are right: using a qualifier results in an error (unknown column):

update TempSel set WertA = 'TEST' where PKey in 
(select Q1.PKey from (select PKey as myKey from TempSel where PKey like'3%') as Q1)

joerg.kannengiesser Old Hand Points: 322 More actions · Answer 4

in th example of course I coul do it like

UPDATE TempSel SET WertA = 'TEST' WHERE PKey LIKE '3%';

the selection for the records to update was much complicater than in my simple example.

so I first made a very complicated selection statement and wanted to use these results for update

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

joerg.kannengiesser wrote:

your are right: using a qualifier results in an error (unknown column):
update TempSel set WertA = 'TEST' where PKey in 
(select Q1.PKey from (select PKey as myKey from TempSel where PKey like'3%') as Q1)

It certainly seems Grant is right. Tho it appears your attempt to alias the columns is maybe not correct. Try these two queries (which are equivalent (afaik)). No sample data so this is not tested...

/* no aliases */
update TempSel set WertA = 'TEST' where PKey in 
(select PKey from (select PKey as myKey from TempSel where PKey like'3%') as Q1)

/* every col aliased */
update ts
set ts.WertA = 'TEST' 
from TempSel ts
where ts.PKey in (select ts.PKey 
                  from (select tt.PKey as myKey 
                        from TempSel tt
                        where tt.PKey like'3%') as Q1);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

joerg.kannengiesser Old Hand Points: 322 More actions · Answer 6

@steve-2: with your statement same problem, because ts.PKey is not column of subquery.

Must be: Q1.myKey

update ts
set ts.WertA = 'TEST' 
from TempSel ts
where ts.PKey in (select Q1.myKey
from (select tt.PKey as myKey 
from TempSel tt
where tt.PKey like'3%') as Q1);

Steve Collins SSCrazy Points: 2157 More actions · Answer 7

Instead of typing up an example I pasted your code next to a table from another topic and then did cut/paste/replace the column names. "...ts.PKey is not column of subquery" the subquery can be correlated to the outer UPDATE statement. Run this

drop table if exists #MDM;
go
CREATE TABLE #MDM (
ID            INT,
Ident         VARCHAR(20),
source        VARCHAR(20),
val           VARCHAR(20),
opendate      datetime,
closedate     datetime,
Isactive      bit);

insert into #MDM (ID,Ident,source ,val,opendate,closedate,Isactive) 
VALUES
(101, 'euid01' ,'File01', 'x010', '01-01-2023', '01-01-2999' ,1),
(101, 'euid02' ,'File01', 'x111', '01-01-2023', '01-01-2999' ,1),
(102, 'euid02' ,'File01', 'x222', '01-01-2023', '01-10-2024' ,0),
(102, 'euid01' ,'File01', 'x010', '01-01-2023', '01-01-2999' ,1),
(102, 'euid02' ,'File02', 'x333', '01-01-2023', '01-01-2999' ,1),
(102, 'euid02' ,'File10', 'x444', '01-01-2023', '01-10-2024' ,0);

update #MDM set val = 'TEST' where ID in 
(select * from (select ID as myKey from #MDM where ID=101) as Q1)

update m 
  set val = 'TEST' 
from #MDM m
where m.ID in 
(select m.ID from (select mm.ID as myKey from #MDM mm where mm.ID=101) as Q1);

This reply was modified 3 weeks, 1 day ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

joerg.kannengiesser Old Hand Points: 322 More actions · Answer 8

testing this the first statement updates 2 rows, the second statement again all rows because m.ID is not a row of the subquery and so does not limit the updates in any case.

changing the 2nd statemant to the folling code does the correct update of only 2 rows:

update m 
set val = 'TEST' 
from #MDM m
where m.ID in 
(select q1.myKey from (select mm.ID as myKey from #MDM mm where mm.ID=101) as Q1);

This reply was modified 3 weeks, 1 day ago by joerg.kannengiesser.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

There should be an additional lesson learned here... when doing modifications to rows, start with BEGIN TRANSACTION and then run the UPDATE. If the rowcount is right, then COMMIT else ROLLBACK. And never walk away from your machine when such a transaction is in-process. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 10

I would use EXISTS instead of IN

update m 
   set val = 'TEST' 
  from #MDM m
 where exists(SELECT 1
                FROM #MDM mm 
               WHERE mm.ID = m.ID
                 AND mm.ID = 101)
;

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

You have just learned a valuable lesson - scope does matter in SQL Server. Sub-queries have full access to the outer queries columns - so you can easily reference a column from the outer query in the sub-query.

You should always alias your tables - and use that table alias for every column.

update TempSel set WertA = 'TEST' where PKey in 
(select PKey from (select PKey as myKey from TempSel where PKey like'3%') as Q1)

In this example - PKey exists in the table TempSel so the sub-query for the IN statement uses PKey from the outer reference to the TempSel table (the one referenced in the UPDATE statement). This essentially breaks down to WHERE PKey IN (PKey) which will be true for every row.

Adding the table alias forces SQL Server to use the column from the inner query - which doesn't exist and you get an error.

This is further complicated because you are using a sub-query that includes a derived table (another sub-query). And since none of the columns are referenced using the table or the alias - SQL Server can and will use any valid column from that sub-query up to the outer-most query.

So - always, always, always qualify columns with the table/alias.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs