[Phpmyadmin-devel] Simulate UPDATE query

[Marc Delisle]

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?

> 
> 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)