Update MYSQL database

Question

I am getting values of a business by the API and inserting it into a database.

SNO       ID            category
1              aaa         Machine learning
2              aaa         AI
3              bbb        mobile

Where SNO is the primary key which is set to auto increment. Now after 2 days, for keeping my database up to date I need to get the new data from the API. So suppose that I come to know that ID ‘aaa’ now has one more category as “data structures”

Question: How can I update my table to reflect this new category? I am expecting something like

SNO       ID            category
1              aaa         Machine learning
2              aaa         AI
3              bbb        mobile
4              aaa         data structures

I don’t know the SNO as they are Auto Incremented. Deleting all the rows which has ID = “aaa” and then Inserting it again is one option but I am trying to avoid that as it might increase the overheads.

I am getting new values from the API,

So I am getting Machine learning, AI and Data structures ( all 3 ) If I use , so in my code when I iterate over the category variable 3 SQL Will be generated

INSERT INTO tablename (ID, category) VALUES ('aaa', ‘Machine learning’);
INSERT INTO tablename (ID, category) VALUES ('aaa', ‘AI’);
INSERT INTO tablename (ID, category) VALUES ('aaa', 'data structures');

So in this case 1st and 2nd insert statement will be duplicated, my table will have duplicate rows . .

I basically need to check if the ID and Category exists in the BD if they do not then INSERT ?( The Primary Key SNO is set to auto Increment)

+ Options                       

Field   Type    Null    Key Default Extra   
uuid    varchar(50) NO      NULL        
categoryName    varchar(50) NO      NULL        
sno int(5)  NO  PRI NULL

It seems MySQL support merge, with nonstandard syntax ; check http://mechanics.flite.com/blog/2013/09/30/how-to-do-an-upsert-in-mysql/ — okaram, Oct 30 '14 at 04:38

score 2 · Answer 1 · answered Oct 30 '14 at 04:39

2

If you just insert the new "data structures" row, it will have the next auto-incremented number, which in your case would likely be 4. The only time it wouldn't be 4 is if you inserted some rows and deleted them, which would cause the auto increment counter to be higher. You could just insert the row like this:

INSERT INTO tablename (ID, category) VALUES ('aaa', 'data structures');

Or maybe I'm not understanding the question correctly. If not reply back and I'll try to help.

answered Oct 30 '14 at 04:39

Mark Madej

1,752
1
14
19

I am getting new values from the API, So I am getting Machine learning, AI and Data structures ( all 3 ) If I use , so in my code when I iterate over the category variable 3 SQL Will be generated INSERT INTO tablename (ID, category) VALUES ('aaa', ‘Machine learning’); INSERT INTO tablename (ID, category) VALUES ('aaa', ‘AI’); INSERT INTO tablename (ID, category) VALUES ('aaa', 'data structures'); So in this case 1st and 2nd insert statement will be duplicated, my table will have duplicate rows – Yahoo Oct 30 '14 at 04:56
1

I'd probably need to know more about the API you are using. However, you have a couple of options. You could always query the table to see if a row already exists (SELECT FROM tablename WHERE ID='aaa' AND category='data structures') and if there's no match, insert. If you post more details about how the API works maybe we can come up with a better solution for you. – Mark Madej Oct 30 '14 at 05:16
Yes, I need to check if its in the Database and if doesn't exist then Insert. What would that SQL look like? INSERT ON DUPLICATE KEY will take into consideration the primary key ? but my primary key is the SNO and not the business ID – Yahoo Oct 30 '14 at 16:39
I think INSERT ON DUPLICATE KEY should work, as long as the business ID (your id column) is also a unique key. If that is the case, this SQL should work for you (sorry one sec, getting used to the stackoverflow comment system) – Mark Madej Oct 31 '14 at 06:43
`INSERT INTO tablename (ID, category) VALUES ('aaa', 'data structures') ON DUPLICATE KEY UPDATE ID=ID` would probably work. Not sure if it's the most elegant solution. But basically this would insert a row, or if it exists, essentially do nothing (set the ID to the value of ID). – Mark Madej Oct 31 '14 at 06:45
And of course, this assumes that the ID column is a unique key. If you want to figure that out, run the SQL `DESC tablename` which will dump out the table structure, and post it here. – Mark Madej Oct 31 '14 at 06:46
Updated the question which the table structure. In this case would ON Duplicate Key work? sno and uuid are both unique in my case. – Yahoo Nov 03 '14 at 16:30
@AdiMathur, it won't work as your table is structured right now. However, run this to make the uuid / categoryName combination unique : `ALTER TABLE tableName ADD UNIQUE INDEX (uuid, categoryName);`. Then when you run updates, run them like this : `INSERT INTO tableName (uuid, categoryName) VALUES ('aaa', 'data structures') ON DUPLICATE KEY UPDATE uuid=uuid;` I just tested this out on SQLFiddle and it worked well. – Mark Madej Nov 05 '14 at 04:36

score 0 · Answer 2 · edited May 23 '17 at 12:34

0

You can simply INSER the new row by background checking or INSERT ... ON DUPLICATE KEY UPDATE Syntax or UPDATE if exist.

edited May 23 '17 at 12:34

Community

1
1

answered Oct 30 '14 at 05:14

Rafiqul Islam

1,636
1
12
25

Update MYSQL database

2 Answers2