[Phpmyadmin-devel] Simulate UPDATE query

[Ashutosh Dhundhara]

On Saturday, 14 June 2014 12:13 AM, Marc Delisle <marc at infomarc.info> wrote:

Ashutosh Dhundhara a écrit :
> Hi Marc, 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'.
> 
> Lets say:
> 
> `table_1` +----+----------+ | id | value    | +----+----------+ | 10
> | value_10 | | 20 | value_20 | +----+----------+
> 
> UPDATE query: UPDATE table_1 SET value = 'value_10' WHERE id = 10; 
> Result: Query OK, 0 rows affected (0.04 sec) Rows matched: 1
> Changed: 0  Warnings: 0
> 
> SELECT query: SELECT * FROM table_1 WHERE id = 10; Result: 1 row in
> set.
> 
> But this query will yield same result: SELECT * FROM table_1 WHERE id
> = 10 AND value <> 'value_10';
> 
> Result: Empty set (0.00 sec)
> 
> Please correct me if I am wrong.
> 
> Regards, Ashutosh Dhundhara
> 

Hi Ashutosh,
(Please use bottom-posting on this list).


I see what you mean. In your original question, do you have a problem 
only when UPDATE or SELECT statement involves two tables?

Hi Marc,

yes this problem arises when there is a JOIN or more than one table in UPDATE statement. I was wondering if we can tweak the SELECT statement to get the desired results.

> 
> On Friday, 13 June 2014 10:42 PM, Marc Delisle <marc at infomarc.info>
> wrote:
> 
> 
> 
> Ashutosh Dhundhara a écrit :
> 
>> Hi, I was working on RFE #861 (Simulate UPDATE query). Lets say I
>> have two tables:
>> 
>> `table_1` +----+----------+ | id | value    | +----+----------+ |
>> 10 | value_10 | | 20 | value_20 | +----+----------+
>> 
>> `table_2` +----+----------+ | id | value    | +----+----------+ |
>> 10 | value_10 | | 20 | value_20 | +----+----------+
>> 
>> 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;
>> 
>> This will affect 2 rows.
>> 
>> How to simulate this query using SELECT statement?
>> 
>> I was trying:
>> 
>> 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;
>> 
>> 
>> but this only returns 1 row. Can this be done in a single query
>> only?
>> 
>> Regards, Ashutosh Dhundhara
> 
> Hi Ashutosh, any reason why in your SELECT statement, you are using
> "<> 'NEW_VALUE'" but in your UPDATE statement, you are using
> "='NEW_VALUE'" ?
> 
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 
> ------------------------------------------------------------------------------
>  HPCC Systems Open Source Big Data Platform from LexisNexis Risk
> Solutions Find What Matters Most in Your Big Data with HPCC Systems 
> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data. Leverages
> Graph Analysis for Fast Processing & Easy Data Exploration 
> http://p.sf.net/sfu/hpccsystems
> 
> 
> ------------------------------------------------------------------------
> 
> 
> _______________________________________________ Phpmyadmin-devel
> mailing list Phpmyadmin-devel at lists.sourceforge.net 
> https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel





-- 
Marc Delisle (phpMyAdmin)

------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems
_______________________________________________
Phpmyadmin-devel mailing list
Phpmyadmin-devel at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel

 
Regards, 
Ashutosh Dhundhara