Update statement gives wrong result with subquery [duplicate]

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

This question already has an answer here:

sql server 2008 management studio not checking the syntax of my query

2 answers

I have the following query which gives no error when I used a non-existent column reference in the subquery. The column which I referred in the subquery is actually a column in the table being updated.

create table tbl1 (f1 bigint, f2 char(10), f3 integer);

insert into tbl1 values (1, 'aa', 0);

insert into tbl1 values (2, 'bb', 0);

insert into tbl1 values (3, 'cc', 0);

insert into tbl1 values (4, 'dd', 0);



create table temp_tbl (ref_num bigint);

insert into temp_tbl values (1);

insert into temp_tbl values (3);



update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

-- 4 records updated

can anyone tell me why it is not giving any error? and records are updated irrespective of the condition.

I tried this in both Oracle and SQLserver. results are same

asked Jan 3 at 11:22

Geek shadow

293

marked as duplicate by Martin Smith sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 3 at 11:25

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add a comment |

This question already has an answer here:

sql server 2008 management studio not checking the syntax of my query

2 answers

create table tbl1 (f1 bigint, f2 char(10), f3 integer);

insert into tbl1 values (1, 'aa', 0);

insert into tbl1 values (2, 'bb', 0);

insert into tbl1 values (3, 'cc', 0);

insert into tbl1 values (4, 'dd', 0);



create table temp_tbl (ref_num bigint);

insert into temp_tbl values (1);

insert into temp_tbl values (3);



update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

-- 4 records updated

can anyone tell me why it is not giving any error? and records are updated irrespective of the condition.

I tried this in both Oracle and SQLserver. results are same

asked Jan 3 at 11:22

Geek shadow

293

marked as duplicate by Martin Smith sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 3 at 11:25

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add a comment |

This question already has an answer here:

sql server 2008 management studio not checking the syntax of my query

2 answers

create table tbl1 (f1 bigint, f2 char(10), f3 integer);

insert into tbl1 values (1, 'aa', 0);

insert into tbl1 values (2, 'bb', 0);

insert into tbl1 values (3, 'cc', 0);

insert into tbl1 values (4, 'dd', 0);



create table temp_tbl (ref_num bigint);

insert into temp_tbl values (1);

insert into temp_tbl values (3);



update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

-- 4 records updated

can anyone tell me why it is not giving any error? and records are updated irrespective of the condition.

I tried this in both Oracle and SQLserver. results are same

asked Jan 3 at 11:22

Geek shadow

293

This question already has an answer here:

sql server 2008 management studio not checking the syntax of my query

2 answers

create table tbl1 (f1 bigint, f2 char(10), f3 integer);

insert into tbl1 values (1, 'aa', 0);

insert into tbl1 values (2, 'bb', 0);

insert into tbl1 values (3, 'cc', 0);

insert into tbl1 values (4, 'dd', 0);



create table temp_tbl (ref_num bigint);

insert into temp_tbl values (1);

insert into temp_tbl values (3);



update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

-- 4 records updated

can anyone tell me why it is not giving any error? and records are updated irrespective of the condition.

I tried this in both Oracle and SQLserver. results are same

This question already has an answer here:

sql server 2008 management studio not checking the syntax of my query

2 answers

sql sql-server oracle sql-update in-subquery

asked Jan 3 at 11:22

Geek shadow

293

asked Jan 3 at 11:22

Geek shadow

293

asked Jan 3 at 11:22

Geek shadow

293

asked Jan 3 at 11:22

Geek shadow

293

asked Jan 3 at 11:22

Geek shadow

293

marked as duplicate by Martin Smith sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 3 at 11:25

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

marked as duplicate by Martin Smith sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 3 at 11:25

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add a comment |

2 Answers
2

active

oldest

votes

The sub-query's column reference goes to the outer table!

update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

Is read as

update tbl1 set f2='ok' where f1 in (select tbl1.f1 from temp_tbl);

Qualify your columns:

update tbl1 set f2='ok' where f1 in (select temp_tbl.ref_num  from temp_tbl);

answered Jan 3 at 11:25

jarlh

30.1k52138

add a comment |

This is happening because the values in a SELECT don't just have to be columns from the table you're selecting from, the sub-query is returning the value for f1 from the outer query, instead of a value from temp_tbl.

Consider if you re-wrote the UPDATE query to:

SELECT  *

FROM    tbl1 

WHERE   f1 IN (select f1 from temp_tbl);

The results returned would actually be:

The results of executing the query

When you're trying to reason about something like this (and as a generally good way of working to get queries right!), it's useful to write your UPDATE query in the form:

UPDATE  T

SET     F2 = 'ok'

FROM    TBL1 T

WHERE   T.f1 IN

        (

            SELECT  F1

            FROM    temp_tbl

        )

By writing it this way you can readily comment out the UPDATE and SET components of the query, replace them with a SELECT and see what the set of data the query will operate on is.

edited Jan 3 at 11:30

answered Jan 3 at 11:25

Rob

37.8k21104131

add a comment |

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

The sub-query's column reference goes to the outer table!

update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

Is read as

update tbl1 set f2='ok' where f1 in (select tbl1.f1 from temp_tbl);

Qualify your columns:

update tbl1 set f2='ok' where f1 in (select temp_tbl.ref_num  from temp_tbl);

answered Jan 3 at 11:25

jarlh

30.1k52138

add a comment |

The sub-query's column reference goes to the outer table!

update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

Is read as

update tbl1 set f2='ok' where f1 in (select tbl1.f1 from temp_tbl);

Qualify your columns:

update tbl1 set f2='ok' where f1 in (select temp_tbl.ref_num  from temp_tbl);

answered Jan 3 at 11:25

jarlh

30.1k52138

add a comment |

The sub-query's column reference goes to the outer table!

update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

Is read as

update tbl1 set f2='ok' where f1 in (select tbl1.f1 from temp_tbl);

Qualify your columns:

update tbl1 set f2='ok' where f1 in (select temp_tbl.ref_num  from temp_tbl);

answered Jan 3 at 11:25

jarlh

30.1k52138

The sub-query's column reference goes to the outer table!

update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

Is read as

update tbl1 set f2='ok' where f1 in (select tbl1.f1 from temp_tbl);

Qualify your columns:

update tbl1 set f2='ok' where f1 in (select temp_tbl.ref_num  from temp_tbl);

answered Jan 3 at 11:25

jarlh

30.1k52138

answered Jan 3 at 11:25

jarlh

30.1k52138

answered Jan 3 at 11:25

jarlh

30.1k52138

answered Jan 3 at 11:25

jarlh

30.1k52138

add a comment |

Consider if you re-wrote the UPDATE query to:

SELECT  *

FROM    tbl1 

WHERE   f1 IN (select f1 from temp_tbl);

The results returned would actually be:

The results of executing the query

When you're trying to reason about something like this (and as a generally good way of working to get queries right!), it's useful to write your UPDATE query in the form:

UPDATE  T

SET     F2 = 'ok'

FROM    TBL1 T

WHERE   T.f1 IN

        (

            SELECT  F1

            FROM    temp_tbl

        )

By writing it this way you can readily comment out the UPDATE and SET components of the query, replace them with a SELECT and see what the set of data the query will operate on is.

edited Jan 3 at 11:30

answered Jan 3 at 11:25

Rob

37.8k21104131

add a comment |

Consider if you re-wrote the UPDATE query to:

SELECT  *

FROM    tbl1 

WHERE   f1 IN (select f1 from temp_tbl);

The results returned would actually be:

The results of executing the query

When you're trying to reason about something like this (and as a generally good way of working to get queries right!), it's useful to write your UPDATE query in the form:

UPDATE  T

SET     F2 = 'ok'

FROM    TBL1 T

WHERE   T.f1 IN

        (

            SELECT  F1

            FROM    temp_tbl

        )

By writing it this way you can readily comment out the UPDATE and SET components of the query, replace them with a SELECT and see what the set of data the query will operate on is.

edited Jan 3 at 11:30

answered Jan 3 at 11:25

Rob

37.8k21104131

add a comment |

Consider if you re-wrote the UPDATE query to:

SELECT  *

FROM    tbl1 

WHERE   f1 IN (select f1 from temp_tbl);

The results returned would actually be:

The results of executing the query

When you're trying to reason about something like this (and as a generally good way of working to get queries right!), it's useful to write your UPDATE query in the form:

UPDATE  T

SET     F2 = 'ok'

FROM    TBL1 T

WHERE   T.f1 IN

        (

            SELECT  F1

            FROM    temp_tbl

        )

By writing it this way you can readily comment out the UPDATE and SET components of the query, replace them with a SELECT and see what the set of data the query will operate on is.

edited Jan 3 at 11:30

answered Jan 3 at 11:25

Rob

37.8k21104131

Consider if you re-wrote the UPDATE query to:

SELECT  *

FROM    tbl1 

WHERE   f1 IN (select f1 from temp_tbl);

The results returned would actually be:

The results of executing the query

When you're trying to reason about something like this (and as a generally good way of working to get queries right!), it's useful to write your UPDATE query in the form:

UPDATE  T

SET     F2 = 'ok'

FROM    TBL1 T

WHERE   T.f1 IN

        (

            SELECT  F1

            FROM    temp_tbl

        )

By writing it this way you can readily comment out the UPDATE and SET components of the query, replace them with a SELECT and see what the set of data the query will operate on is.

edited Jan 3 at 11:30

answered Jan 3 at 11:25

Rob

37.8k21104131

edited Jan 3 at 11:30

answered Jan 3 at 11:25

Rob

37.8k21104131

answered Jan 3 at 11:25

Rob

37.8k21104131

answered Jan 3 at 11:25

Rob

37.8k21104131

add a comment |

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu