How to copy data between 2 tables with an extra field in first table which has to be updated by a default value?

Question

I have 2 tables Table NAME (id, name, phone, city, state, rid) Table NAMES2 (id, name, phone, city)

This SQL statment is not working: INSERT INTO NAME (id, name, phone, city, rid) VALUES ((SELECT id, name, phone, citY from NAMES2 WHERE city="Bangalore"),'72')

I want rid field in Table NAME to have 72 for all the records inserted from table NAMES2. Note: both the tables are not identical.

Answer 1

Try this query -

INSERT INTO NAME (id, name, phone, city, rid)
  SELECT id, name, phone, citY, 72 FROM NAMES2 WHERE city = 'Bangalore'

If field rid has default value 72 (run SHOW CREATE TABLE to view it), then you can use this query -

INSERT INTO NAME (id, name, phone, city)
  SELECT id, name, phone, citY FROM NAMES2 WHERE city = 'Bangalore'