[Phpmyadmin-devel] Simulate UPDATE query

[Marc Delisle]

Le 2014-06-14 12:05, Ashutosh Dhundhara a écrit :
> 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.

Hi Ashutosh,
I have no solution for this; maybe this means that the simulation will
have to be offered only for one-table queries.

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