upload just new rows from one table to another one

Question

Hi I know how to upload data from one table to another one:

INSERT INTO new_table (id, pricerange, rentrange, date)
SELECT id, price, rent, date
FROM initial_table

But I have a problem with this if you consider I have 10 rows in my initial table by this code I can upload all of them into new table and then after for example 10 hours if I have 10 new rows and I use this code in my new table I will have 30 rows. Because this code did not delete 10 old row and also add all 20 rows again. what can I do that not to upload first 10 row again?

Possible duplicate of [Insert into a MySQL table or update if exists](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — StudioTime, Jul 04 '16 at 11:08
why u even Need to pull out 10 rows and store in next table ? — KikiTheOne, Jul 04 '16 at 11:15
it has a long story. i am desining advertising website and i have many ads from different cases like cars, realestate and electrical devises . thus i have 3 different tables with different column number in my database. one for each of them. i want to show all ads in my website first page and also order them by date after a lot of search i decide to create a total table and insert my tables data into it so i can show all of my ads in first page. — Malekian, Jul 04 '16 at 11:20

score 0 · Answer 1 · answered Jul 04 '16 at 11:08

0

Set a flag variable in the database. By default set the value as 0. If the data uploaded then set that value to 1. When you are upload one table to another check the flag value. Upload only those value which are 0.

answered Jul 04 '16 at 11:08

Avishake

460
1
6
20

thank you for your answering could you please show it with a simple code – Malekian Jul 04 '16 at 11:13
I don't know your database structure. And I also don't know the code which you are using to upload data from one table to another. So, give some code what you did, I will help you according to that. What I give, is an idea. According to your database structure and code the idea will implemented. – Avishake Jul 04 '16 at 11:17

score 0 · Answer 2 · answered Jul 04 '16 at 11:09

0

Is there a primary key defined? Or if you can have a time stamp field / system change number on the table you can capture the scn / time stamp in a separate table after your first insert and get the records from base table by comparing the time stamp against the preserved one.

answered Jul 04 '16 at 11:09

Praneeth Gudumasu

175
8

thank you for your answering could you please show it with a simple code – Malekian Jul 04 '16 at 11:12

score 0 · Accepted Answer · edited Mar 08 '20 at 10:47

0

You can use this query. This will insert only those rows which are not present in first table:

  INSERT INTO new_table(id, pricerange, rentrange, date) SELECT id, price, rent, date  FROM initial_table WHERE NOT EXISTS(SELECT * FROM new_table WHERE (initial_table.id=new_table.id))

You can add more condition in "if" statement if you want

edited Mar 08 '20 at 10:47

halfer

19,824
17
99
186

answered Jul 04 '16 at 11:38

Passionate Coder

7,154
2
19
44

upload just new rows from one table to another one

3 Answers3