Comparing data from two different tables mysql inserts new data and updates data that does not match

Question

I am trying to compare data from different tables, insert new data and update data that do not match.

Example:

I have table1 
------------------------------------
| ITEMNO | DESCRIPTION | FORSALE |
------------------------------------
| 123456 | Description1 | YES |
------------------------------------
| 234567 | Description2 | YES |
------------------------------------
| 345678 | Description3 | YES |
------------------------------------

and I also have table2, which is just a temporary table

----------
| ITEMNO |
----------
| 123456 |
----------
| 234567 |
----------

ITEMNO - primary key in table1 and foreign key in table2

So, when the module sends data from the program and first checks table2, and then compares the data with table 1

if the data sent is similar to

  table2 table1
---------- ----------
| ITEMNO |    | ITEMNO |
----------    ----------
| 123456 |  = | 123456 | (MATCH WITH TABLE1 AND TABLE2 THEN UPDATE)  
----------    ----------
| 234567 |  = | 234567 | (MATCH WITH TABLE1 AND TABLE2 THEN UPDATE)
----------    ----------
| 567890 |  = | 567890 | (NEW DATA THEN INSERT INTO TABLE 1)
----------    ----------
              | 345678 | (DOESN'T EXIST IN TABLE2 BUT EXISTS IN TABLE 1 SO UPDATE FORSALE FIELD TO "NO")
              ----------

, .

Answer 1

2 sql -

UPDATE table1 AS t1 
LEFT JOIN table2 AS t2 ON t1.itemno=t2.itemno 
SET t1.FORSALE='No' 
WHERE t2.itemno IS NULL;

INSERT IGNORE INTO table2 (itemno) SELECT t1.itemno 
FROM table1 AS t1 
LEFT JOIN table2 AS t2 ON t1.itemno=t2.itemno 
WHERE t2.itemno IS NULL;

, , , -

SELECT t1.itemno 
FROM table1 AS t1 
LEFT JOIN table2 AS t2 ON t1.itemno=t2.itemno 
WHERE t2.itemno IS NULL;

Answer 2

2 . :

//NEW DATA THEN INSERT INTO TABLE 1
insert into table1(item_no,description)
select item_no,description
from table2
where item_no not in(
    select item_no from table1
);

//DOESN'T EXIST IN TABLE2 BUT EXISTS IN TABLE 1 SO UPDATE FORSALE FIELD TO "NO"
update table1
set forsale = 'NO'
where item_no not in(
    select item_no from table2
)

, TABLE1 AND TABLE2 .