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@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'" ?