MySQL INSERT record using SELECT

Question

I have two tables tblcity and tblcityx:

--
-- Table structure for table `tblcity`
--

CREATE TABLE IF NOT EXISTS `tblcity` (
  `pkcityid` bigint(20) NOT NULL AUTO_INCREMENT,
  `cityname` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `fkstateid` bigint(20) NOT NULL,
  PRIMARY KEY (`pkcityid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `tblcityx`
--

CREATE TABLE IF NOT EXISTS `tblcityx` (
 `pkcityid` bigint(20) NOT NULL AUTO_INCREMENT,
 `cityname` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
 `fkstateid` bigint(20) NOT NULL,
 PRIMARY KEY (`pkcityid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

I am trying to add a new record in tblcity from tblcityx. Currently, I have tried:

$cityid = 10; // could be any city id
INSERT INTO `tblcity` (SELECT * FROM `tblcityx` WHERE pkcityid = '$cityid')

Both tables have same number of fields and pkcityid is auto-incremented.

It adds a new DUPLICATE record in tblcity as it has already a city with pkcityid=10, so after running the above query there are two records with pkcityid=10.

Isn't there a way that even if the tblcity already has a city with that pkcityid, it should always add a NEW record?

Might be straight forward for an expert but I'm unable to find a way through.

pkcityid should be primary key, or at least have a UNIQUE constraint. Can you change that? Otherwise, there is no simple way to do that with a simple SQL statement (you could still do what you want with a convoluted stored procedure, or with an external script). — RandomSeed
– RandomSeed, Commented Jul 24, 2014 at 8:41
pkcityid is primary key in both tables but the problem stands still. — Adil Abbas
– Adil Abbas, Commented Jul 24, 2014 at 13:22
Then how comes it "adds a new DUPLICATE record in tblcity"?! Or did you mean it raises a DUPLICATE error ? — RandomSeed
– RandomSeed, Commented Jul 24, 2014 at 13:30
a DUPLICATE record has been inserted that was may be due UNIQUE constraint was not been set. — Adil Abbas
– Adil Abbas, Commented Jul 24, 2014 at 13:41
You are right it should have prompted me with a DUPLICATE error. — Adil Abbas
– Adil Abbas, Commented Jul 24, 2014 at 13:43

RandomSeed · Accepted Answer · 2014-07-25 10:29:49Z

1

You can make MySQL generate a new auto-incremented value by providing NULL as value for the AUTO_INCREMENT column:

INSERT INTO target_table
    SELECT NULL, cityname, fkstateid
    FROM source_table
    WHERE pkcityid = @your_id;

note: the AUTO_INCREMENTcolumn must be defined as NOT NULL for this to work

You probably want to check for the existence of @your_id in the target table. Something along the lines of:

if (@your_id does not exist in target_table) {
    INSERT INTO target_table SELECT * ...
} else {
    INSERT INTO target_table SELECT NULL, cityname ...
}

answered Jul 25, 2014 at 10:29

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RandomSeed Over a year ago

Check the Reference manual

Adil Abbas Over a year ago

Works a treat! Exactly, what I wanted. Can't believe it was that simple at the end. :)

Merlin Denker · Accepted Answer · 2014-07-24 07:53:14Z

1

This is not possible if pkcityid is your primary key. Primary Keys can only exist once in a table. You may add a timestamp field that is automatically set to the current timestamp when inserting a row and add that to the key. then you have a combined key of id and timestamp, which will be unique when inserting a new record.

answered Jul 24, 2014 at 7:53

Merlin Denker

1,41811 silver badges15 bronze badges

3 Comments

Adil Abbas Over a year ago

you mean in tblcityx pkcityid should not be a primary key? Unfortunately, both tables have the same structure.

Merlin Denker Over a year ago

The field can be a primary key, but you will need another field which also is a primary key. You can not have identical records if there is a primary key on any field. If you add a timestamp field that indicates when the record was added and make that a primary key as well it will work.

Adil Abbas Over a year ago

Unfortunately, I can not add fields in the tables. In that case I guess, you are right, it is not possible.

Bhavesh · Accepted Answer · 2014-07-24 08:19:58Z

1

Here is the example. you can use the column name which you wants

Like this

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';

check this example you will get to know http://www.w3schools.com/sql/sql_insert_into_select.asp

if both table having the same stucture than you can use the alias for remove the naming ambiguity.

here is example "how to give alias name"

http://www.w3schools.com/sql/sql_alias.asp

edited Jul 24, 2014 at 8:19

answered Jul 24, 2014 at 8:00

Bhavesh

1147 bronze badges

3 Comments

Adil Abbas Over a year ago

Actually, both tables have the same structure so this query is not solving my case, I have already tried this!

Bhavesh Over a year ago

if both table having the same stucture than you can use the alias for remove the naming ambiguity. here is example "how to give alias name" w3schools.com/sql/sql_alias.asp

Adil Abbas Over a year ago

we can not use aliases for INSERT queries, and for the SELECT query I introduce an alias, it will still get that pkcityid e.g, 10.

Collectives™ on Stack Overflow

MySQL INSERT record using SELECT

3 Answers 3

2 Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related