0

How would one go about updating a column in MySQL with column data from an unrelated table?

No common fields so tables cannot be joined.

Column data is all unique and needs to be copied in order.

Have tried:

UPDATE NewTable 
SET NewTableID = (SELECT OldTableID
FROM OldTable);

As I understand this would try and update all values in NewTableID to one value from OldTableID - But would fail as it returns more than one value.

I want to take ALL unique OldTableID values in order, and update NewTableID so that it mirrors OldTableID exactly. OldTableID is AUTO_INCREMENT if that changes anything.

Edit 1

Incident Table

The IncidentID of this Incident table is a PK

Crime Table

I wanted to update it into the FK in the Crime Table

IncidentID is AUTO_INCREMENT

Insert causes the data to be added as new rows. I cant insert all data at once as I am importing from CSV

James Fisher
  • 87
  • 9
  • what's inside the NewTable? for a fresh table just do a insert into select... – RoMEoMusTDiE Jan 14 '18 at 22:21
  • 1
    This is a little unclear. Do you already have data in the NewTable? and if so, are you trying to update the NewTableID and somehow retrain the columns that the current NewTableID has? Why would you want to do this? Perhaps there is an alternate solution for what you are trying to achieve. – Bob Aleena Jan 14 '18 at 22:22
  • @BobAleena This is exactly right. I already have other columns of NewTable populated. Im trying to copy in the FK data that refers to the PK which is OldTableID. – James Fisher Jan 14 '18 at 22:30
  • 1
    Edit your question and provide sample data and desired results. What you want to do is not an update. – Gordon Linoff Jan 14 '18 at 22:31
  • This is a little confusing -- so you want to update each item, where the FK and PK match? That should be straightforward update. or are you wanting to also alter the table to have the columns from OldTable? – Bob Aleena Jan 14 '18 at 22:35
  • It sounds like you need to do the following. Algorithm: Get the each column name and type from oldTable (see: https://stackoverflow.com/questions/13405572/sql-statement-to-get-column-type) For each column name and type, Alter your newtable to also have that column/type. Start transferring. update newTable where PK in oldtable = FK in NewTable. Is that what you are trying to achieve? – Bob Aleena Jan 14 '18 at 22:40
  • @BobAleena Im sorry, I'm not conveying myself very well as I am very much still a beginner. I literally just want to add in the FK data so that it can link to the PK of the 'Incident' Table which is 'IncidentID'. I have set the reference so that FK points to PK in the table settings - I just need to add the data. Currently I can only use INSERT which add the rows afterwards and loses the rest of the columns I have imported. – James Fisher Jan 14 '18 at 22:49
  • IncidentId and CrimeId should be the same in the new table? – Marcelo Origoni Jan 14 '18 at 23:04
  • @MarceloOrigoni yes – James Fisher Jan 14 '18 at 23:19

1 Answers1

1

for a fresh NewTable just do

insert into NewTable (id)
select id from OldTable
RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26