13 Jun
2014
13 Jun
'14
8 p.m.
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
13 Jun
13 Jun
8:11 p.m.
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'" ?
--
Marc Delisle (phpMyAdmin)
8:26 p.m.
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
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'" ?
--
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(a)lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
9:42 p.m.
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(a)infomarc.info>
wrote:
Ashutosh Dhundhara a écrit :
--
Marc Delisle (phpMyAdmin)
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(a)lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
14 Jun
14 Jun
7:05 p.m.
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.
On Friday, 13 June 2014 10:42 PM, Marc Delisle <marc(a)infomarc.info>
wrote:
Ashutosh Dhundhara a écrit :
--
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(a)lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
Regards,
Ashutosh Dhundhara
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(a)lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
7:39 p.m.
Le 2014-06-14 12:05, Ashutosh Dhundhara a écrit :
--
Marc Delisle | phpMyAdmin
On Saturday, 14 June 2014 12:13 AM, Marc Delisle
<marc(a)infomarc.info>
wrote:
Ashutosh Dhundhara a écrit :
Hi Ashutosh,
I have no solution for this; maybe this means that the simulation will
have to be offered only for one-table queries.
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(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
mailing list
Phpmyadmin-devel(a)lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
16 Jun
16 Jun
6:45 p.m.
2014-06-13 19:00 GMT+02:00 Ashutosh Dhundhara <ashutoshdhundhara(a)yahoo.com>om>:
Hi,
I was working on RFE #861
<https://sourceforge.net/p/phpmyadmin/feature-requests/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,
Isn't it possible to run the query with
set AUTOCOMMIT=0;
and a rollback at the end?
Hugues.
6:50 p.m.
On Mon, Jun 16, 2014 at 9:15 PM, Hugues Peccatte <hugues.peccatte(a)gmail.com>
wrote:
2014-06-13 19:00 GMT+02:00 Ashutosh Dhundhara
<ashutoshdhundhara(a)yahoo.com
Hi,
I doubt that it will work for non innodb databases.
:
Hi,
I was working on RFE #861
<https://sourceforge.net/p/phpmyadmin/feature-requests/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,
Isn't it possible to run the query with
set AUTOCOMMIT=0;
and a rollback at the end?
Hugues.
--
Regards,
Chirayu Chiripal
6:59 p.m.
Hugues Peccatte a écrit :
2014-06-13 19:00 GMT+02:00 Ashutosh Dhundhara
<ashutoshdhundhara(a)yahoo.com>om>:
Thanks Hugues. In the "simulate query" subtask [0] for this student, one
of the feature requests is [1] which involves rolling back (but thanks
for the mention of AUTOCOMMIT=0). But this is only for transactional
storage engines.
Another aspect of this subtask is [2] which involves a simulation via
SELECT.
[0]
https://wiki.phpmyadmin.net/pma/GSoC_2014_Ideas_List#Interface_improvements
[1] http://sourceforge.net/p/phpmyadmin/feature-requests/869/
[2] http://sourceforge.net/p/phpmyadmin/feature-requests/861/
--
Marc Delisle (phpMyAdmin)
Hi,
I was working on RFE #861
<https://sourceforge.net/p/phpmyadmin/feature-requests/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,
Isn't it possible to run the query with
set AUTOCOMMIT=0;
and a rollback at the end?
Hugues.
3745
days inactive
3748
days old
8 comments
4 participants
participants (4)
-
Ashutosh Dhundhara -
Chirayu Chiripal -
Hugues Peccatte -
Marc Delisle