Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update mysql table with select query from another database

Tags:

sql

php

mysql

I have two databases and I want to update one table with values from another database table. I am using the following query but it does not work.

UPDATE database1.table1
SET field2 = database2.table1.field2
WHERE database1.table1.field1 = database2.table1.field1

I have also tried the following query but it does not work either:

UPDATE database1.table1
SET field2 = "SELECT field2 FROM database2.table1"
WHERE database1.table1.field1 = database2.table1.field1
like image 408
mmdel Avatar asked May 22 '13 06:05

mmdel


People also ask

How do I update from a select in MySQL?

Method 2: UPDATE from SELECT: The MERGE statementUse a MERGE statement for updating data in the [Employee] table. It then references another table when the USING clause is applied. The WHEN MATCHED then specifies the merge JOIN (Inner Join) between the source and target table.

Can we use select and update together in MySQL?

Just add them together: UPDATE LOG SET TIME_EXIT = '2013/02/22' WHERE ID = ( SELECT ID FROM employee ORDER BY ID DESC LIMIT ); But based on that code currently it'll only ever update the last employee , you will need to select the correct employee by using some other identifier to ensure you have the correct one.

How can I update data from one table to another table?

We can update the table using UPDATE statement in SQL. The update statement is always followed by the SET command. The SET command is used to specify which columns and values need to be updated in a table.


1 Answers

UPDATE 1

based on your comment, markup should be part of the join. Here's the correct one:

UPDATE oman.ProductMaster_T
    INNER JOIN main.ProductMaster_T 
        ON main.ProductMaster_T.ProductID = oman.ProductMaster_T.ProductID 
SET oman.ProductMaster_T.Markup = main.ProductMaster_T.Markup

you can even add an ALIAS to simplify the statement,

UPDATE oman.ProductMaster_T o
    INNER JOIN main.ProductMaster_T m 
        ON m.ProductID = o.ProductID 
SET o.Markup = m.Markup
like image 185
John Woo Avatar answered Nov 15 '22 15:11

John Woo