CODEWITHSUNDEEP
mysql
gom
gom

Reputation: 897

How to implement this in MySQL?

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.

Upvotes: 0

Views: 99

Answers (1)

cdhowie
cdhowie

Reputation: 169413

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 = ?;

Upvotes: 1

Related Questions