CODEWITHSUNDEEP
mysqlsqlsqlyog
yai
yai

Reputation: 80

Transfer data from 1 column to another from different table with middle table as link

I have 3 tables with table C.Bid referencing to B.id and B.Aid referencing to A.id. I want to transfer data from columns in table A to table C but having issues with the sql.

enter image description here

Heres what i did

UPDATE contact_address,
(SELECT contact_master.id , contact_master.address, 
contact_branch.contact_master_id, contact_branch.id
FROM contact_master
INNER JOIN contact_branch ON contact_master.id = 
contact_branch.contact_master_id)
SET contact_address.address = contact_master.address
WHERE contact_address.contact_branch_id = contact_branch.id;

My understanding from the UPDATE Statement, is the comma can be used to separate different column we want to add in the select statement.

How do i transfer these data column using referenced attribute in the middle table? error im getting is

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET contact_address.address = contact_master.address where contact_address.cont' at line 5

I already have the rows of data on table C where i insert them using these query.

INSERT INTO `contact_branch` (contact_master_id)
SELECT `id` FROM `contact_master`;

INSERT INTO `contact_address` (contact_branch_id)
SELECT `id` FROM `contact_branch`;

Upvotes: 1

Views: 64

Answers (1)

yai
yai

Reputation: 80

Solved it, giving AS to subquery, and AS for middle table (conatct_branch) id so it won't confuse the program.

UPDATE contact_address,
(SELECT contact_master.*, contact_branch.id AS cId
FROM contact_master
INNER JOIN contact_branch ON contact_master.id = contact_branch.contact_master_id) AS subque
SET contact_address.address = subque.address
WHERE contact_address.contact_branch_id = subque.cId;

Upvotes: 0

Related Questions