[firebird-support] update all records
hello! I've been googling but found nothing about it. Why this sentence does not update all the records in the table? (FB 2.5) update stock set actualizar = 0 where 1=1 Thanks!! Sergio
Re: [firebird-support] update all records
Thanks
On Wednesday, May 4, 2016 6:45 PM, "shg_siste...@yahoo.com.ar
[firebird-support]" wrote:
hello! I've been googling but found nothing about it.
Why this sentence does not update all the records in the table? (FB 2.5)
update stock set actualizar = 0 where 1=1
Thanks!!
Sergio #yiv5869094069 #yiv5869094069 -- #yiv5869094069ygrp-mkp {border:1px
solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5869094069
#yiv5869094069ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5869094069
#yiv5869094069ygrp-mkp #yiv5869094069hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv5869094069 #yiv5869094069ygrp-mkp #yiv5869094069ads
{margin-bottom:10px;}#yiv5869094069 #yiv5869094069ygrp-mkp .yiv5869094069ad
{padding:0 0;}#yiv5869094069 #yiv5869094069ygrp-mkp .yiv5869094069ad p
{margin:0;}#yiv5869094069 #yiv5869094069ygrp-mkp .yiv5869094069ad a
{color:#ff;text-decoration:none;}#yiv5869094069 #yiv5869094069ygrp-sponsor
#yiv5869094069ygrp-lc {font-family:Arial;}#yiv5869094069
#yiv5869094069ygrp-sponsor #yiv5869094069ygrp-lc #yiv5869094069hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5869094069
#yiv5869094069ygrp-sponsor #yiv5869094069ygrp-lc .yiv5869094069ad
{margin-bottom:10px;padding:0 0;}#yiv5869094069 #yiv5869094069actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5869094069
#yiv5869094069activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5869094069
#yiv5869094069activity span {font-weight:700;}#yiv5869094069
#yiv5869094069activity span:first-child
{text-transform:uppercase;}#yiv5869094069 #yiv5869094069activity span a
{color:#5085b6;text-decoration:none;}#yiv5869094069 #yiv5869094069activity span
span {color:#ff7900;}#yiv5869094069 #yiv5869094069activity span
.yiv5869094069underline {text-decoration:underline;}#yiv5869094069
.yiv5869094069attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv5869094069 .yiv5869094069attach div a
{text-decoration:none;}#yiv5869094069 .yiv5869094069attach img
{border:none;padding-right:5px;}#yiv5869094069 .yiv5869094069attach label
{display:block;margin-bottom:5px;}#yiv5869094069 .yiv5869094069attach label a
{text-decoration:none;}#yiv5869094069 blockquote {margin:0 0 0
4px;}#yiv5869094069 .yiv5869094069bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv5869094069
.yiv5869094069bold a {text-decoration:none;}#yiv5869094069 dd.yiv5869094069last
p a {font-family:Verdana;font-weight:700;}#yiv5869094069 dd.yiv5869094069last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5869094069
dd.yiv5869094069last p span.yiv5869094069yshortcuts
{margin-right:0;}#yiv5869094069 div.yiv5869094069attach-table div div a
{text-decoration:none;}#yiv5869094069 div.yiv5869094069attach-table
{width:400px;}#yiv5869094069 div.yiv5869094069file-title a, #yiv5869094069
div.yiv5869094069file-title a:active, #yiv5869094069
div.yiv5869094069file-title a:hover, #yiv5869094069 div.yiv5869094069file-title
a:visited {text-decoration:none;}#yiv5869094069 div.yiv5869094069photo-title a,
#yiv5869094069 div.yiv5869094069photo-title a:active, #yiv5869094069
div.yiv5869094069photo-title a:hover, #yiv5869094069
div.yiv5869094069photo-title a:visited {text-decoration:none;}#yiv5869094069
div#yiv5869094069ygrp-mlmsg #yiv5869094069ygrp-msg p a
span.yiv5869094069yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5869094069
.yiv5869094069green {color:#628c2a;}#yiv5869094069 .yiv5869094069MsoNormal
{margin:0 0 0 0;}#yiv5869094069 o {font-size:0;}#yiv5869094069
#yiv5869094069photos div {float:left;width:72px;}#yiv5869094069
#yiv5869094069photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv5869094069
#yiv5869094069photos div label
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5869094069
#yiv5869094069reco-category {font-size:77%;}#yiv5869094069
#yiv5869094069reco-desc {font-size:77%;}#yiv5869094069 .yiv5869094069replbq
{margin:4px;}#yiv5869094069 #yiv5869094069ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv5869094069 #yiv5869094069ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5869094069
#yiv5869094069ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5869094069
#yiv5869094069ygrp-mlmsg select, #yiv5869094069 input, #yiv5869094069 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv5869094069
#yiv5869094069ygrp-mlmsg pre, #yiv5869094069 code {font:115%
monospace;}#yiv5869094069 #yiv5869094069ygrp-mlmsg *
{line-height:1.22em;}#yiv5869094069 #yiv5869094069ygrp-mlmsg #yiv5869094069logo
{padding-bottom:10px;}#yiv5869094069 #yiv5869094069ygrp-msg p a
{font-family:Verdana;}#yiv5869094069 #yiv5869094069ygrp-msg
p#yiv5869094069attach-count span {color:#1E66AE;font-weight:700;}#yiv5869094069
#
Re: [firebird-support] update all records
Hi, one of possible reason is that records are not visible to transaction with this update statement. e.g. 1. you start transansction (tr1) 2. someone start transaction 2(tr2) 3. tr2 insert some records 4. tr1 run update 5. tr2 commit 6. tr1 commit all records inserted by tr2 are not updated if tr1 is e.g. snapshot or during tr1 update there are inserts. regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Wednesday, May 4, 2016 8:45 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] update all records hello! I've been googling but found nothing about it. Why this sentence does not update all the records in the table? (FB 2.5) update stock set actualizar = 0 where 1=1 Thanks!! Sergio
Re: [firebird-support] update all records
Hello,
I don't understand your SQL :"Where 1=1" first "1" is a field name ?
if "Where actualizar=1" it may works ?The condition is on records, and must use
fields values...no ?
If you don't use condition, all records should be updated to the same value 0...
"Update stock set actualizar=0" should update all records with actualizar =0
to whole stock table...
With best regards,Mikey
Le Jeudi 5 mai 2016 19h19, "kristinwens...@yahoo.com [firebird-support]"
a écrit :
Thanks
On Wednesday, May 4, 2016 6:45 PM, "shg_siste...@yahoo.com.ar
[firebird-support]" wrote:
hello! I've been googling but found nothing about it.
Why this sentence does not update all the records in the table? (FB 2.5)
update stock set actualizar = 0 where 1=1
Thanks!!
Sergio
#yiv9733264172 #yiv9733264172 -- #yiv9733264172ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9733264172
#yiv9733264172ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9733264172
#yiv9733264172ygrp-mkp #yiv9733264172hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv9733264172 #yiv9733264172ygrp-mkp #yiv9733264172ads
{margin-bottom:10px;}#yiv9733264172 #yiv9733264172ygrp-mkp .yiv9733264172ad
{padding:0 0;}#yiv9733264172 #yiv9733264172ygrp-mkp .yiv9733264172ad p
{margin:0;}#yiv9733264172 #yiv9733264172ygrp-mkp .yiv9733264172ad a
{color:#ff;text-decoration:none;}#yiv9733264172 #yiv9733264172ygrp-sponsor
#yiv9733264172ygrp-lc {font-family:Arial;}#yiv9733264172
#yiv9733264172ygrp-sponsor #yiv9733264172ygrp-lc #yiv9733264172hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9733264172
#yiv9733264172ygrp-sponsor #yiv9733264172ygrp-lc .yiv9733264172ad
{margin-bottom:10px;padding:0 0;}#yiv9733264172 #yiv9733264172actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9733264172
#yiv9733264172activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9733264172
#yiv9733264172activity span {font-weight:700;}#yiv9733264172
#yiv9733264172activity span:first-child
{text-transform:uppercase;}#yiv9733264172 #yiv9733264172activity span a
{color:#5085b6;text-decoration:none;}#yiv9733264172 #yiv9733264172activity span
span {color:#ff7900;}#yiv9733264172 #yiv9733264172activity span
.yiv9733264172underline {text-decoration:underline;}#yiv9733264172
.yiv9733264172attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv9733264172 .yiv9733264172attach div a
{text-decoration:none;}#yiv9733264172 .yiv9733264172attach img
{border:none;padding-right:5px;}#yiv9733264172 .yiv9733264172attach label
{display:block;margin-bottom:5px;}#yiv9733264172 .yiv9733264172attach label a
{text-decoration:none;}#yiv9733264172 blockquote {margin:0 0 0
4px;}#yiv9733264172 .yiv9733264172bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv9733264172
.yiv9733264172bold a {text-decoration:none;}#yiv9733264172 dd.yiv9733264172last
p a {font-family:Verdana;font-weight:700;}#yiv9733264172 dd.yiv9733264172last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9733264172
dd.yiv9733264172last p span.yiv9733264172yshortcuts
{margin-right:0;}#yiv9733264172 div.yiv9733264172attach-table div div a
{text-decoration:none;}#yiv9733264172 div.yiv9733264172attach-table
{width:400px;}#yiv9733264172 div.yiv9733264172file-title a, #yiv9733264172
div.yiv9733264172file-title a:active, #yiv9733264172
div.yiv9733264172file-title a:hover, #yiv9733264172 div.yiv9733264172file-title
a:visited {text-decoration:none;}#yiv9733264172 div.yiv9733264172photo-title a,
#yiv9733264172 div.yiv9733264172photo-title a:active, #yiv9733264172
div.yiv9733264172photo-title a:hover, #yiv9733264172
div.yiv9733264172photo-title a:visited {text-decoration:none;}#yiv9733264172
div#yiv9733264172ygrp-mlmsg #yiv9733264172ygrp-msg p a
span.yiv9733264172yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9733264172
.yiv9733264172green {color:#628c2a;}#yiv9733264172 .yiv9733264172MsoNormal
{margin:0 0 0 0;}#yiv9733264172 o {font-size:0;}#yiv9733264172
#yiv9733264172photos div {float:left;width:72px;}#yiv9733264172
#yiv9733264172photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv9733264172
#yiv9733264172photos div label
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9733264172
#yiv9733264172reco-category {font-size:77%;}#yiv9733264172
#yiv9733264172reco-desc {font-size:77%;}#yiv9733264172 .yiv9733264172replbq
{margin:4px;}#yiv9733264172 #yiv9733264172ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv9733264172 #yiv9733264172ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9733264172
#yiv9733264172ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9733264172
#yiv9733264172ygrp-mlmsg select, #yiv9733264172 inpu
Re: [firebird-support] update all records
Hi, 1=1 => true “where true” is always true this is oposite to 1=0 => false and this statements are equal: update stock set actualizar = 0 where 1=1; update stock set actualizar = 0; regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Thursday, May 5, 2016 9:55 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] update all records Hello, I don't understand your SQL : "Where 1=1" first "1" is a field name ? if "Where actualizar=1" it may works ? The condition is on records, and must use fields values...no ? If you don't use condition, all records should be updated to the same value 0... "Update stock set actualizar=0" should update all records with actualizar =0 to whole stock table... With best regards, Mikey Le Jeudi 5 mai 2016 19h19, "kristinwens...@yahoo.com [firebird-support]" a écrit : Thanks On Wednesday, May 4, 2016 6:45 PM, "shg_siste...@yahoo.com.ar [firebird-support]" wrote: hello! I've been googling but found nothing about it. Why this sentence does not update all the records in the table? (FB 2.5) update stock set actualizar = 0 where 1=1 Thanks!! Sergio
Re: [firebird-support] update all records
Sergio asked why this statement update stock set actualizar = 0 where 1=1 doesn't update all records in the stock table. First, the clause "where 1=1" is unnecessary. The update statement does not requires a where clause and in the absence of a where clause, it affects all records in the table. , liviusliv...@poczta.onet.pl [firebird-support] responded > one of possible reason is that records are not visible to transaction with > this update statement. > e.g. > 1. you start transaction (tr1) > 2. someone start transaction 2(tr2) > 3. tr2 insert some records > 4. tr1 run update > 5. tr2 commit > 6. tr1 commit > all records inserted by tr2 are not updated if tr1 is e.g. snapshot > or during tr1 update there are inserts. > Actually, that's not true. If tr2 inserts records before tr1 attempts its update, tr1 will discover an update conflict when it finds one of tr2's new records. If tr1 is a no-wait transaction, it will report an error immediately otherwise it will wait for tr2's commit. Then, if tr1 is a snapshot transaction, when tr2 commits, tr1 will report an error. If tr1 is a read-committed transaction, it will silently update tr2's new records without having looked at them.* If, however, the sequence is that both transactions start, then tr1 does its update and tr2 stores its new records after the update completes, both transactions will succeed and the records inserted by tr2 will not be updated. Which would also be the case if tr1 ran to completion before tr2 started. What's slightly more likely is that tr1 and tr2 are concurrent and the sequence of actions is that tr1 modifies all the stock records then tr2 reads all the stock records and sees the old versions (assuming that tr2 is not a "no record version" transaction). Even if tr2 is a read-committed transaction, it will read the next older version of records if the most recent is not committed. So, Sergio, a bit more information would help us give you a better answer. Are there multiple transactions running? Why do you think some records weren't updated? Do you know what transaction options you're using? Cheers, Ann * If the inserts and update are running at the same time, the most likely case is that tr2's inserts will be physically the last entries in the table. Tr1's updates will be made in storage order, so the last thing records it tries to modify will be the records tr2 created. If the table has had lots of records deleted, it's possible (I think) for tr2 to store its records on partially empty pages in the first part of the table and avoid a collision with tr1 if tr1 has already modified the records if found on those pages. That's very similar to having tr1 perform its update before tr2 stores its records.
Re: Re: [firebird-support] update all records
, liviusliv...@poczta.onet.pl [firebird-support] responded one of possible reason is that records are not visible to transaction with this update statement. e.g. 1. you start transaction (tr1) 2. someone start transaction 2(tr2) 3. tr2 insert some records 4. tr1 run update 5. tr2 commit 6. tr1 commit all records inserted by tr2 are not updated if tr1 is e.g. snapshot or during tr1 update there are inserts. Actually, that's not true. If tr2 inserts records before tr1 attempts its update, tr1 will discover an update conflict when it finds one of tr2's new records. If tr1 is a no-wait transaction, it will report an error immediately otherwise it will wait for tr2's commit. Then, if tr1 is a snapshot transaction, when tr2 commits, tr1 will report an error. If tr1 is a read-committed transaction, it will silently update tr2's new records without having looked at them.* If, however, the sequence is that both transactions start, then tr1 does its update and tr2 stores its new records after the update completes, both transactions will succeed and the records inserted by tr2 will not be updated. Which would also be the case if tr1 ran to completion before tr2 started. What's slightly more likely is that tr1 and tr2 are concurrent and the sequence of actions is that tr1 modifies all the stock records then tr2 reads all the stock records and sees the old versions (assuming that tr2 is not a "no record version" transaction). Even if tr2 is a read-committed transaction, it will read the next older version of records if the most recent is not committed. So, Sergio, a bit more information would help us give you a better answer. Are there multiple transactions running? Why do you think some records weren't updated? Do you know what transaction options you're using? Cheers, Ann _,___ Hi Ann, this is not true. CREATE TABLE TEST (ID INTEGER); commit; insert into test(ID) VALUES(1); insert into test(ID) VALUES(2); insert into test(ID) VALUES(3); insert into test(ID) VALUES(4); insert into test(ID) VALUES(5); commit; 1. start snapshot transaction tr1 2. start snapshot transaction tr2 3. in tr2 insert record insert into test(ID) VALUES(10); 4. in tr1 update all records update test set ID=ID + 100; 5. commit tr2 6. commit tr1 you got no error and table with data: ID 101 102 103 104 105 10!!! tested with flamerobin (default flamerobin settings: transaction isolation "CONCURENCY ISOLATION MODE" +WAIT FOR LOCK RESOLUTION) + FB3 regards, Karol Bieniaszewski
Re: Re: [firebird-support] update all records
On Fri, May 6, 2016 at 2:05 AM, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > > > He wrote: > > all records inserted by tr2 are not updated if tr1 is e.g. snapshot > or during tr1 update there are inserts. > > I wrote: > > Actually, that's not true. > > > And I was wrong and lazy. Should have tested it. Records inserted by one transaction will not be affected by an update by another concurrent transaction. My apologies. Best regards, Ann > >
