MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1(
   sale_id INTEGER  NOT NULL PRIMARY KEY 
  ,type  VARCHAR(6) NOT NULL
);
INSERT INTO table1(sale_id,type) VALUES (110,'Credit');
INSERT INTO table1(sale_id,type) VALUES (111,'Cash');
INSERT INTO table1(sale_id,type) VALUES (112,'Credit');


CREATE TABLE table2(
   sales_id INTEGER  NOT NULL PRIMARY KEY 
  ,item_id  VARCHAR(6) NOT NULL
  ,price  VARCHAR(6) NOT NULL
  ,payment_type  VARCHAR(6) NOT NULL
  ,total_amount  VARCHAR(6) NOT NULL
);

INSERT INTO table2(sales_id,item_id,price,payment_type,total_amount) VALUES (110,1,20,'','');
INSERT INTO table2(sales_id,item_id,price,payment_type,total_amount) VALUES (111,2,30,'','');
INSERT INTO table2(sales_id,item_id,price,payment_type,total_amount) VALUES (112,3,45,'','');
INSERT INTO table2(sales_id,item_id,price,payment_type,total_amount) VALUES (112,4,15,'','');






select * from table1;
select * from table2


sale_id
type


110
Credit

111
Cash

112
Credit




sales_id
item_id
price
payment_type
total_amount


110
1
20



111
2
30



112
3
45




UPDATE table2 SET total_amount = 
    (SELECT price
        FROM table2
        INNER JOIN table1 ON sale_id = sales_id
        );
UPDATE table2 SET payment_type = 
    (SELECT type
        FROM table2
        INNER JOIN table1 ON sale_id = sales_id
        );        
        
        
You can't specify target table 'table2' for update in FROM clause

select * from table2


sales_id
item_id
price
payment_type
total_amount


110
1
20



111
2
30



112
3
45