mysql copy data from one table to another using foreign key

February 15, 2022

inputPreviewOffsetLeft, inputPreviewOffsetTop, inputPreviewSizeWidth, inputPreviewSizeHeight are present in the display_preview table and display. display_preview has a foreign key displayId on display table.

INSERT INTO display_preview b (b.inputPreviewOffsetLeft, b.inputPreviewOffsetTop, b.inputPreviewSizeWidth, b.inputPreviewSizeHeight)
    SELECT bd.inputPreviewOffsetLeft, bd.inputPreviewOffsetTop, bd.inputPreviewSizeWidth, bd.inputPreviewSizeHeight 
    FROM display
INNER JOIN display bd on bd.id = b.displayId

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'b (b.inputPreviewOffsetLeft, b.inputPreviewOffsetTop, b.inputPreviewSizeWidth...' at line 1

>Solution :

You don’t need to get INSERT table an alias name and I think you might want to use INSERT INTO ... SELECT on display_preview and display

you can try the below query

INSERT INTO display_preview (inputPreviewOffsetLeft,inputPreviewOffsetTop, inputPreviewSizeWidth, inputPreviewSizeHeight)
SELECT bd.inputPreviewOffsetLeft, bd.inputPreviewOffsetTop, bd.inputPreviewSizeWidth, bd.inputPreviewSizeHeight 
FROM display_preview b
INNER JOIN display bd on bd.id = b.displayId

EDIT

If you want to do UPDATE ... JOIN you can try this.

UPDATE display_preview b
INNER JOIN display bd on bd.id = b.displayId
SET 
    b.inputPreviewOffsetLeft = bd.inputPreviewOffsetLeft, 
    b.inputPreviewOffsetTop = bd.inputPreviewOffsetTop, 
    b.inputPreviewSizeWidth = bd.inputPreviewSizeWidth , 
    b.inputPreviewSizeHeight = bd.inputPreviewSizeHeight

For more detail you can refer to MySQL UPDATE JOIN