Le 2014-06-14 12:05, Ashutosh Dhundhara a écrit :
On Saturday, 14 June 2014 12:13 AM, Marc Delisle
<marc(a)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(a)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
--
Marc Delisle | phpMyAdmin