MySQL insert into column data from another table

Question

I have 2 tables:

   original {ID, FirstName, LastName}
   and
   dummy {ID(NULL), FirstName, LastName}

I have to insert into t2.ID the original.ID but only if the FirstName and LastName from both tables are the same. Now, i've tried:

1.Error Code: 1054. Unknown column 't2.FirstName' in 'where clause'

INSERT INTO dummy (ID)
SELECT ID
FROM original
WHERE dummy.FirstName = original.FirstName
    AND dummy.LastName = original.LastName;

2.Error Code: 1054. Unknown column 'original.FirstName' in 'where clause'

UPDATE dummy
SET ID = original.ID
WHERE dummy.FirstName=original.FirstName
   AND dummy.LastName= original.LastName;

3.Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

NOTE: I have to find a way without disableing safe mode.

UPDATE dummy
   JOIN original
   ON original.FirstName = dummy.FirstName
       AND original.LastName = dummy.LastName
SET dummy.IDPacient = original.ID
WHERE original.ID <> 0;

Now if someone could help me understand what i did wrong in each of these 3 cases and/or give me a better solution, i would much appreciate the help.

score 2 · Accepted Answer · answered Aug 11 '16 at 23:02

2

Version 1 is just plain wrong - you'll insert a new row, not update the existing row.

Version 2 is close, you just need a join to "original":

UPDATE dummy
SET ID = original.ID
FROM dummy
INNER JOIN original
  ON dummy.FirstName =original.FirstName
  AND dummy.LastName = original.LastName;

answered Aug 11 '16 at 23:02

ADyson

57,178
14
51
63

thanks for your help. But there is something off here, i get a syntax error at `FROM` telling me that `is not a valid input at this position` – MRM Aug 12 '16 at 20:20
I've passed that error eliminating from querry `FROM` and moving `set` at the end. – MRM Aug 12 '16 at 21:33

score 1 · Answer 2 · edited May 23 '17 at 12:01

You need to perform a join on first and last name between the "original" and "dummy" tables and then update the ID

Try this..

Update a
Set a.ID = b.ID
From dummy a
Join original b
On a.firstname = b.firstname
And b.lastname = b.firstname

You were trying to filter based on columns in the "original" table but it wasn't included in a from clause in your query.

This link might also have some more useful info for you if you need it. SQL update query using joins

MySQL insert into column data from another table

2 Answers2