0

This is what I wish to do using MySQL alone: (without PHP)

PHP Code:

$result=mysql_query("select f2 from t1 where f1=constant limit L"); while( $data = mysql_fetch_row($result) ) mysql_query("update t2 set f4=f4-1 where f3={$data[0]} limit 1");

If you don't know PHP:

select f2 from t1 where f1=constant limit L; for each f2 (from above result) as F: update t2 set f4=f4-1 where f3=F limit 1;

How to implement this in MySQL?

Details:

There can be duplicate values in the output of first select, and for each of the duplicates, the update statement will be run. So if value '123' occurs thrice in the output of first select then f4 will be updated to f4-3 where f3=123.

'L' is the number of times the "f1=constant" condition is satisfied in table 't1'; this number is known before hence used as a limit.

limit 1 is used because values in field 'f3' are unique.

Improve this question

1 Answer 1

Reset to default
1

Use this query, and supply the missing parameters:

UPDATE t2 SET f4 = f4 - 1 WHERE f3 IN (SELECT f2 FROM t1 WHERE f1 = ? LIMIT ?);

If you want to have t2.f4 modified for each row in f2 that matches (given that f2.t1 is not unique) then this should work:

UPDATE t2, t1 SET t2.f4 = t2.f4 - 1 WHERE t2.f3 = t1.f2 AND t1.f1 = ?;
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

If the select statement inside parenthesis gives duplicate values then will update be done for each of the duplicate values?
And how to tell MySQL here that for each "where in", it'll be finding only one record in t2? (As I'm doing in my question)
@user1444680 If there is a unique index on t2.f3 then you don't need a LIMIT clause on the update, since only one row can be updated per value. WHERE ... IN will ignore duplicate values in the subquery, but you can change the subquery to SELECT DISTINCT if this makes you feel better. (That change will not alter the behavior of the query.)
But the unique index will slow down inserts? Sir I've mentioned in my question (1st point under Details) that I wish that for every duplicate value, the update must be repeated. What do you suggest?
If there are duplicate values in t2.f3 then those will be affected by this query too, which seems to be what you want anyway.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.