issue with UPDATE

[lbo...@gmail.com]

I'm stuck with an UPDATE, probably I'm messing something up:


the amount of rows in the '`proj_los`' table expected to be updated are 32, but only 8 get updated, unless I add an AND condition.

select count(*) from import.tmp_kk where root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e';
> 32




1) correct result -> using last AND clause (`root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e'`)


sql> update
proj_los as oko
set lo_root_id = import.tmp_kk.root_id
from import.tmp_kk
where oko.lo_id = import.tmp_kk.lo_id
and import.tmp_kk.root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e'
[2018-10-05 18:13:59] 32 rows affected in 50 ms


select count(*) from proj_los where lo_root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e';

> 32




2) wrong result

sql> update
proj_los as oko
set lo_root_id = import.tmp_kk.root_id
from import.tmp_kk
where oko.lo_id = import.tmp_kk.lo_id;
[2018-10-05 18:17:31] 174202 rows affected in 17 s 427 ms

select count(*) from proj_los where lo_root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e';

> 8

Any help on this?

[Robert Klemme]

On 05.10.2018 18:44, lbo...@gmail.com wrote:
> I'm stuck with an UPDATE, probably I'm messing something up:
>
>
> the amount of rows in the '`proj_los`' table expected to be updated are 32, but only 8 get updated,

No, 174202 get updated.

> unless I add an AND condition.
>
> select count(*) from import.tmp_kk where root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e';
> > 32
>
>
>
>
> 1) correct result -> using last AND clause (`root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e'`)
>
>
> sql> update
> proj_los as oko
> set lo_root_id = import.tmp_kk.root_id
> from import.tmp_kk
> where oko.lo_id = import.tmp_kk.lo_id
> and import.tmp_kk.root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e'
> [2018-10-05 18:13:59] 32 rows affected in 50 ms
>
>
> select count(*) from proj_los where lo_root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e';
>
> > 32
>
>
>
>
> 2) wrong result
>
> sql> update
> proj_los as oko
> set lo_root_id = import.tmp_kk.root_id
> from import.tmp_kk
> where oko.lo_id = import.tmp_kk.lo_id;
> [2018-10-05 18:17:31] 174202 rows affected in 17 s 427 ms
>
> select count(*) from proj_los where lo_root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e';
>
> > 8
>
> Any help on this?
>

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/