update table2 with some values from table1

Viewed 26

i want to update table2 and fill some column with data from table 1

i can do that with INSERT and is working well but i can't figure out how can it works if i need to update table

THIS WORKS:

 INSERT INTO table2 (endTime, end_lat, end_lng, end_phone_lat, end_phone_lng)
 SELECT NOW() as endTime, lat, lng, 'staticTXT1', 'staticTXT2' 
    FROM table1    
    WHERE tbl1_ID=121

so for update i need to do something like this (logically) but is not working

UPDATE table2 
        (endTime, end_lat, end_lng, end_phone_lat, end_phone_lng)
    SELECT NOW() as endTime, lat, lng, 'staticTXT1', 'staticTXT2' 
    FROM table 1  
 WHERE tbl1_ID=121 
 ORDER BY tbl2_ID DESC 
 LIMIT 1;

maybe i need to do this but i think is not best way

   UPDATE table2 
        set endTime=NOW(),  end_phone_lat='1', end_phone_lng='2', 
    end_lat= (
            SELECT lat
            from table1
            WHERE tbl1_id=10
        ),end_lng = (
            SELECT lng
            from table1
            WHERE tbl1_id=10
        )

 WHERE cid=121 
 ORDER BY tbl2_ID DESC 
 LIMIT 1;
0 Answers
Related