<html><body><div style="color:#000; background-color:#fff; font-family:arial, helvetica, sans-serif;font-size:10pt"><div class="" style=""><span class="" style="">Hi Marc,</span></div><div style="color: rgb(0, 0, 0); font-size: 13px; font-family: arial, helvetica, sans-serif; font-style: normal; background-color: transparent;" class=""><span class="" style="">I think if we will not use "<> 'NEW_VALUE'" in SELECT statement, then it will also return the rows which match the WHERE clause but already have the corresponding column's value = 'NEW_VALUE'. But the UPDATE statement won't count those rows in affected rows where there is already value = 'NEW_VALUE'.</span></div><div style="color: rgb(0, 0, 0); font-size: 13px; font-family: arial, helvetica, sans-serif; font-style: normal; background-color: transparent;" class=""><span class="" style=""><br class="" style=""></span></div><div style="color: rgb(0, 0, 0); font-size: 13px; font-family: arial,
helvetica, sans-serif; font-style: normal; background-color: transparent;" class="">Lets say:</div><div style="color: rgb(0, 0, 0); font-size: 13px; font-family: arial, helvetica, sans-serif; font-style: normal; background-color: transparent;" class=""><br class="" style=""></div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;">`table_1`</span></div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;">+----+----------+</span></div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;">| id | value |</span></div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace,
sans-serif;">+----+----------+</span></div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;">| 10 | value_10 |</span></div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;">| 20 | value_20 |</span></div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;"><font class="" style=""></font></span></div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;">+----+----------+</span></div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;"><br class="" style=""></span></div><div class=""
style="background-color: transparent;"><font face="Courier New, courier, monaco, monospace, sans-serif" class="" style="">UPDATE query: UPDATE table_1 SET value = 'value_10' WHERE id = 10;</font></div><div class="" style="background-color: transparent;"><font face="Courier New, courier, monaco, monospace, sans-serif" class="" style="">Result:</font></div><div class="" style="background-color: transparent;"><div class="" style="background-color: transparent;">Query OK, 0 rows affected (0.04 sec)</div><div class="" style="background-color: transparent;">Rows matched: 1 Changed: 0 Warnings: 0</div></div><div class="" style="background-color: transparent;"><br class="" style=""></div><div class="" style="background-color: transparent;">SELECT query: <span style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;" class="">SELECT * FROM table_1 WHERE id = 10;</span></div><div class="" style="background-color:
transparent;">Result: 1 row in set.</div><div class="" style="background-color: transparent;"><br class="" style=""></div><div class="" style="background-color: transparent;">But this query will yield same result:</div><div class="" style="background-color: transparent;"><span class="" style="font-family: 'Courier New', courier, monaco, monospace, sans-serif;">SELECT * FROM table_1 WHERE id = 10 AND value <> 'value_10';</span><br class="" style=""></div><div class="" style="background-color: transparent;"><span class="" style="">Result: </span><span style="font-size: 10pt; background-color: transparent;" class="">Empty set (0.00 sec)</span></div><div class="" style="background-color: transparent;"><span class="" style=""><br class="" style=""></span></div><div class="" style="background-color: transparent;">Please correct me if I am wrong.</div><div class="" style=""></div><div class="" style=""> </div><div class="" style="">Regards,
<br class="" style="">Ashutosh Dhundhara</div> <div class="qtdSeparateBR"><br><br></div><div class="yahoo_quoted" style="display: block;"> <div style="font-family: arial, helvetica, sans-serif; font-size: 10pt;" class=""> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;" class=""> <div dir="ltr" class="" style=""> <font size="2" face="Arial" class="" style=""> On Friday, 13 June 2014 10:42 PM, Marc Delisle <marc@infomarc.info> wrote:<br class="" style=""> </font> </div> <br class="" style=""><br class="" style=""> <div class="" style="">Ashutosh Dhundhara a écrit :<div class="" id="yqtfd03184" style=""><br clear="none" class="" style="">> Hi,<br clear="none" class="" style="">> I was working on RFE #861 (Simulate UPDATE query).<br clear="none" class="" style="">> Lets say I have two tables:<br clear="none" class="" style="">> <br clear="none" class="" style="">> `table_1`<br clear="none" class=""
style="">> +----+----------+<br clear="none" class="" style="">> | id | value |<br clear="none" class="" style="">> +----+----------+<br clear="none" class="" style="">> | 10 | value_10 |<br clear="none" class="" style="">> | 20 | value_20 |<br clear="none" class="" style="">> +----+----------+<br clear="none" class="" style="">> <br clear="none" class="" style="">> `table_2`<br clear="none" class="" style="">> +----+----------+<br clear="none" class="" style="">> | id | value |<br clear="none" class="" style="">> +----+----------+<br clear="none" class="" style="">> | 10 | value_10 |<br clear="none" class="" style="">> | 20 | value_20 |<br clear="none" class="" style="">> +----+----------+<br clear="none" class="" style="">> <br clear="none" class="" style="">> UPDATE Query: UPDATE table_1, table_2 SET table_1.value = 'NEW_VALUE', table_2.value='NEW_VALUE' WHERE table_1.id >
10 AND table_2.id > 10;<br clear="none" class="" style="">> <br clear="none" class="" style="">> This will affect 2 rows.<br clear="none" class="" style="">> <br clear="none" class="" style="">> How to simulate this query using SELECT statement?<br clear="none" class="" style="">> <br clear="none" class="" style="">> I was trying:<br clear="none" class="" style="">> <br clear="none" class="" style="">> SELECT DISTINCT table_1.value, table_2.value FROM table_1, table_2 WHERE table_1.value <> 'NEW_VALUE' AND table_2.value <> 'NEW_VAUE' AND table_1.id > 10 AND table_2.id > 10;<br clear="none" class="" style="">> <br clear="none" class="" style="">> <br clear="none" class="" style="">> but this only returns 1 row.<br clear="none" class="" style="">> Can this be done in a single query only?<br clear="none" class="" style="">> <br clear="none" class="" style="">> Regards, <br clear="none"
class="" style="">> Ashutosh Dhundhara</div><br clear="none" class="" style=""><br clear="none" class="" style="">Hi Ashutosh,<br clear="none" class="" style="">any reason why in your SELECT statement, you are using "<> 'NEW_VALUE'" <br clear="none" class="" style="">but in your UPDATE statement, you are using "='NEW_VALUE'" ?<br clear="none" class="" style=""><br clear="none" class="" style=""><br clear="none" class="" style="">-- <br clear="none" class="" style="">Marc Delisle (phpMyAdmin)<br clear="none" class="" style=""><br clear="none" class="" style="">------------------------------------------------------------------------------<br clear="none" class="" style="">HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions<br clear="none" class="" style="">Find What Matters Most in Your Big Data with HPCC Systems<br clear="none" class="" style="">Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.<br clear="none"
class="" style="">Leverages Graph Analysis for Fast Processing & Easy Data Exploration<br clear="none" class="" style=""><a shape="rect" href="http://p.sf.net/sfu/hpccsystems" target="_blank" class="" style="">http://p.sf.net/sfu/hpccsystems</a><br clear="none" class="" style="">_______________________________________________<br clear="none" class="" style="">Phpmyadmin-devel mailing list<br clear="none" class="" style=""><a shape="rect" ymailto="mailto:Phpmyadmin-devel@lists.sourceforge.net" href="mailto:Phpmyadmin-devel@lists.sourceforge.net" class="" style="">Phpmyadmin-devel@lists.sourceforge.net</a><br clear="none" class="" style=""><a shape="rect" href="https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel" target="_blank" class="" style="">https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel</a><br class="" style=""><br class="" style=""></div> </div> </div> </div> </div></body></html>