将值分配给sql查询中的列

Question

I have 2 tables and what I need is to selelct some of the columns from table1 and copy those columns in the table2. I have tried many options but nothing is working for me. I am a beginner in the php and mysql field . These are the things that I have tried first.

 `$sql = " UPDATE table1 JOIN table2 ON  table1.primary_key = 
table2.primary_key  SET table1.column1= table2.column1 , table1.column2= 
table2.column2  WHERE table1.primary_key = 1  ";

and

`$sql= "update table1 set table1.column1 = 
table2.column1  FROM table1 INNER JOIN  
            table2 on table1.primary_key = 
 table2.primary_key WHERE table2.primary_key= 1 ";

and because of the reason that bothof them are not working, I tried another method:

              $sql= "SELECT column1,column2FROM table2 WHERE primary_key=1";
              $result = mysql_query($sql);
              $row = mysql_fetch_array($result);`
              $UpdateQuery ="UPDATE  table1 SET column1=$row[column1] ,column2=$row[column2] WHERE primary_key= 1";
                mysql_query($UpdateQuery);

This is also throwing an error . Can any one tel me how to assign the column value from sql query or ny other methods that will satisfy my need .

Answer 1

One option is to use a JOIN inside your SQL query during the UPDATE:

UPDATE table1 t1
JOIN
(
    SELECT * FROM table2
    WHERE table2.primary_key = 1
) t2
ON t1.primary_key = t2.primary_key
SET t1.column1 = t2.column1

I will leave it to you to insert this into your PHP code since so many details are not present in the original problem.