CODEWITHSUNDEEP
mysqlsqldatabase
user3900026
user3900026

Reputation: 41

Updating rows by getting value from another Database

What is the best procedure to update the rows by getting value from another database.

Below is the query that i am using for that.

UPDATE live_client_production.measurement_attachment t1
INNER JOIN live_client_uk.measurement_attachment t2
   ON t2.active = t1.active
  SET t1.content = t2.content 
where t1.id=97 
  AND t2.id=1;

For the next value to update the values for t1.id and t2.id are given

UPDATE live_client_production.measurement_attachment t1 
 INNER JOIN live_client_uk.measurement_attachment t2 ON t2.active = t1.active 
   SET t1.content = t2.content 
 where t1.id=98 
   AND t2.id=2;

Is there any way to set a limit or range so that t1.id and t2.id gets automatically incremented to a certain range.

Whats the best procedure to do this in mysql.

I humbly request you to please help me

Upvotes: 1

Views: 53

Answers (3)

drunken_monkey
drunken_monkey

Reputation: 1858

If I understand this correctly, bot ids should increment by 1 for each new update.

if you wanted, you could do just one update and make sure t1.id is always t2.id-96.

...
WHERE t1.id = t2.id-96

If you rely on iterating through several updates in one procedure, you can use variables, which you can restrict. Example:

set @id1 = 1;
set @id2 = 97;

while @id < 95 do

    UPDATE live_client_production.measurement_attachment t1
    INNER JOIN live_client_uk.measurement_attachment t2
    ON t2.active = t1.active
    SET t1.content = t2.content 
    where t1.id=97 
    AND t2.id=1;

    set @id1 = @id1+1;
    set @id2 = @id2+1;
end while;

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

You can filter on the relative difference of the Id columns, and set a ceiling.

Something like

WHERE t1.id=t2.id+96
AND t1.id < @SomeCeilingValue

Upvotes: 0

DerekCate
DerekCate

Reputation: 306

Potentially you can do a 

where t1.id = (t2.id+96);

This makes me nervous though, because it is assuming it is always 96 between the two ids. I'd be more comfortable if you could have the id's be the same, so that it was 98 in both tables, or if there were some code in both tables that identified them as being for the same data. Then you have

where t1.id = t2.id

or 

where t1.code = t2.code

Upvotes: 1

Related Questions