Any SQL gurus in the house? Can you help? - GoFuckYourself.com

Phil · 09-24-2008, 03:44 PM

I have two identical structure DBs. One has 1000+ records (DB1) the other one that has 200 (DB2). I would like to combine those two by adding 1000 records to smaller DB skipping duplicates (based on primary key - ID).. so basically moving 800 records starting with ID=201 from DB1 to DB2

Babaganoosh · 09-24-2008, 03:47 PM

If the primary key is unique you could do a regular insert and mysql will just throw an error and continue when it sees a duplicate id.

Phil · 09-24-2008, 06:29 PM

mysql will error out n that. Im using insert ingnore should work but I'm missing something there. screw it, will do it tomorrow.

BTW, why isn't "rollback" command working in MySql?

GrouchyAdmin · 09-24-2008, 06:32 PM

select * into db2 from db1 where (id <= 201 and id >= 1000)?

brandonstills · 09-24-2008, 07:09 PM

1. Get the list of ids on server #1
2. Combine them into a comma separated list: join(', ', $theList);
3. "SELECT * FROM table WHERE id NOT IN ($theList)"

Varius · 09-24-2008, 07:13 PM

Quote:

Originally Posted by GrouchyAdmin

select * into db2 from db1 where (id <= 201 and id >= 1000)?

Won't work if the IDs that are in one but not the other aren't sequential

Have to use a join (right, left, outer, whatever way you prefer hehe) in that case.

EDIT: My bad, he did say they are starting with 201 heh. So nevermind.

gornyhuy · 09-24-2008, 07:15 PM

http://bogdan.org.ua/2007/10/18/mysq...ts-syntax.html

pstation · 09-24-2008, 07:20 PM

Quote:

Originally Posted by CAMOKAT

mysql will error out n that. Im using insert ingnore should work but I'm missing something there. screw it, will do it tomorrow.

BTW, why isn't "rollback" command working in MySql?

are you using INNODB? myisam does not support transactions

sarettah · 09-24-2008, 07:20 PM

INSERT INTO db2 SELECT * FROM db1 WHERE id NOT IN (SELECT id FROM db2)

Lane · 09-24-2008, 07:23 PM

why didn't "insert ignore" work?

mikesouth · 09-24-2008, 07:24 PM

im not sure I see the problem a simple unload of one database to a file then insert it into the other. If you want more control unload both to flat files, merge and sort (UNIX) then and use the uniq command(UNIX) to dedupe the merged file then load it into a database

GigoloShawn · 09-24-2008, 10:08 PM

Quote:

Originally Posted by mikesouth

im not sure I see the problem a simple unload of one database to a file then insert it into the other. If you want more control unload both to flat files, merge and sort (UNIX) then and use the uniq command(UNIX) to dedupe the merged file then load it into a database

Well, that's one way to entirely avoid the hassle of, uh, the database part.

Owen Pierce · 09-24-2008, 10:16 PM

uhm... you said "DB1" and "DB2".. so.. I'm gonna assume you actually mean databases, although it seems more likely you mean tables... anyways.

INSERT INTO
db1.table
(field1,field2,field3)
SELECT
t2.field1,
t2.field2,
t2.field3
FROM
db1.table t2
LEFT OUTER JOIN db2.table t1 ON (t1.id = t2.id)
WHERE
t1.id IS NULL

Owen Pierce · 09-24-2008, 10:17 PM

Quote:

Originally Posted by sarettah

INSERT INTO db2 SELECT * FROM db1 WHERE id NOT IN (SELECT id FROM db2)

sub-selects? YIKES!

Owen Pierce · 09-24-2008, 10:21 PM

Quote:

Originally Posted by mikesouth

im not sure I see the problem a simple unload of one database to a file then insert it into the other. If you want more control unload both to flat files, merge and sort (UNIX) then and use the uniq command(UNIX) to dedupe the merged file then load it into a database

that doesn't work if he's in INNODB...
and uhm... doing some file based process to fix database issues is just asking for problems.

not to mention, db files are binary... he's not doing a local RSYNC or something...

GrouchyAdmin · 09-24-2008, 10:23 PM

Quote:

Originally Posted by Owen Pierce

that doesn't work if he's in INNODB...
and uhm... doing some file based process to fix database issues is just asking for problems.

not to mention, db files are binary... he's not doing a local RSYNC or something...

I pray to god he was at least assuming doing two dumps.. but the whole idea of doing that just has to be tongue in cheek.

mrkris · 09-24-2008, 11:20 PM

You all make shit way to complex.

mikesouth · 09-24-2008, 11:51 PM

Quote:

Originally Posted by Owen Pierce

that doesn't work if he's in INNODB...
and uhm... doing some file based process to fix database issues is just asking for problems.

not to mention, db files are binary... he's not doing a local RSYNC or something...

nah its a cakewalk and way faster than cross database transfers assuming his dbms even allows such things

I also assumed he really meant databases and not relations (tables)

It mostly just a matter of understanding your data dbload and dbunload seem simplest.

GrouchyAdmin · 09-24-2008, 11:52 PM

Quote:

Originally Posted by mrkris

You all make shit way to complex.

You smell like Grandma.

Phil · 09-25-2008, 12:02 AM

I had 4 beers tonight, so Im not touching it until tomorrow, but please tell me why "rollback" isn't working for MySql. Is it oracle only feature? does it do commit on it own???

Phil · 09-25-2008, 12:05 AM

Quote:

Originally Posted by Owen Pierce

uhm... you said "DB1" and "DB2".. so.. I'm gonna assume you actually mean databases, although it seems more likely you mean tables... anyways.

INSERT INTO
db1.table
(field1,field2,field3)
SELECT
t2.field1,
t2.field2,
t2.field3
FROM
db1.table t2
LEFT OUTER JOIN db2.table t1 ON (t1.id = t2.id)
WHERE
t1.id IS NULL

databases.. each has identical structure.. I want to combine entries...

PPC · 09-25-2008, 12:09 AM

export both into excel

and select all data then goto toolbar Data > Filter > Advanced Filter

click copy to another location change the copy to range ... click unique records only then hit ok

but hey, I'm no SQL guru :p

(btw this will only work for up to like 65k, excels row limit)

Phil · 09-25-2008, 12:12 AM

Quote:

Originally Posted by PPC

export both into excel

and select all data then goto toolbar Data > Filter > Advanced Filter

click copy to another location change the copy to range ... click unique records only then hit ok

but hey, I'm no SQL guru :p

(btw this will only work for up to like 65k, excels row limit)

thanks man...

GrouchyAdmin · 09-25-2008, 12:13 AM

Or, ya know, you could just take my query and insert the field/values but NOT the auto_increment key field for X records.

But, hey, I haven't seen so many convoluted ways to do database transactions OUTSIDE of a database in.. forever.

rowan · 09-25-2008, 01:36 AM

Quote:

Originally Posted by GrouchyAdmin

Or, ya know, you could just take my query and insert the field/values but NOT the auto_increment key field for X records.

But, hey, I haven't seen so many convoluted ways to do database transactions OUTSIDE of a database in.. forever.

A CSV file still has "data" in it! :D

budz · 09-25-2008, 04:02 AM

Quote:

Originally Posted by rowan

A CSV file still has "data" in it! :D

funny.. I was thinking something like that about excel

http://spreadsheets.about.com/od/dat...l_database.htm

V_RocKs · 09-25-2008, 04:58 AM

Or just select all from the 1000 and select all from 200 where id = id and if it don't match, insert

sarettah · 09-25-2008, 05:53 AM

Quote:

INSERT INTO db2 SELECT * FROM db1 WHERE id NOT IN (SELECT id FROM db2)

Quote:

Originally Posted by Owen Pierce

sub-selects? YIKES!

Yep. Pretty quick, simple and straight forward.

And your issue with sub-selects is ??

grumpy · 09-25-2008, 07:51 AM

some real geniuses here

Owen Pierce · 09-25-2008, 08:27 AM

Quote:

Originally Posted by CAMOKAT

databases.. each has identical structure.. I want to combine entries...

well if the tables in each DB are the same.. then..

INSERT INTO
db1.table
(*)
SELECT
t2.*
FROM
db1.table t2
LEFT OUTER JOIN db2.table t1 ON (t1.id = t2.id)
WHERE
t1.id IS NULL

just make sure the permissions of the user you are runnign this as has access to both db's.

Owen Pierce · 09-25-2008, 08:33 AM

Quote:

Originally Posted by sarettah

Yep. Pretty quick, simple and straight forward.

And your issue with sub-selects is ??

they are super slow, bad practice. I wish MySQL never put that damn functionality into itself.

when I have to fix other peoples legacy code.. finding queries that take 14 seconds for no reason at all.. it's usually due to sub-selects.
OR bad index's...
but most sub-selects

Owen Pierce · 09-25-2008, 08:34 AM

Quote:

Originally Posted by mikesouth

nah its a cakewalk and way faster than cross database transfers assuming his dbms even allows such things

I also assumed he really meant databases and not relations (tables)

It mostly just a matter of understanding your data dbload and dbunload seem simplest.

quicker?
hehe, obviously you're a *nix guy and not a DBA...

sarettah · 09-25-2008, 08:47 AM

Quote:

Originally Posted by Owen Pierce

they are super slow, bad practice. I wish MySQL never put that damn functionality into itself.

when I have to fix other peoples legacy code.. finding queries that take 14 seconds for no reason at all.. it's usually due to sub-selects.
OR bad index's...
but most sub-selects

Yeah, but this sounds like he wanted something for one time use not something that was going to be running in an ongoing process.

When it is one time use take the easiest route.

mikesouth · 09-25-2008, 09:26 AM

Quote:

Originally Posted by Owen Pierce

quicker?
hehe, obviously you're a *nix guy and not a DBA...

actually I was a sr consultant for Informix for yrs but ya I got lots of UNIX too thing is my informix days were back in 1993 and I havent really kept up with RDBMS since...

Varius · 09-25-2008, 10:21 AM

You could always use INSERT IGNORE also, to just try and insert them all from db1 and it will error on any duplicates (primary key), BUT keep going and not stop if you are using INSERT IGNORE

react · 09-25-2008, 10:34 AM

Yes, stop fucking stringing us along and tell us why INSERT IGNORE is not an option.

Owen Pierce · 09-25-2008, 06:58 PM

Quote:

Originally Posted by mikesouth

actually I was a sr consultant for Informix for yrs but ya I got lots of UNIX too thing is my informix days were back in 1993 and I havent really kept up with RDBMS since...

either way, you're way doesn't work...
cuz if he's using INNODB, it can't be done outside of SQL.
if he's using INNODB then he wouldn't need any sort of comparison, he could just flat out copy the file from one DB to the other.
AND that's all assuming he has access to the server, not just SQL access. AND that's assuming he knows how to navigate around *nix at all.

Owen Pierce · 09-25-2008, 07:00 PM

Quote:

Originally Posted by sarettah

Yeah, but this sounds like he wanted something for one time use not something that was going to be running in an ongoing process.

When it is one time use take the easiest route.

It's still poison. ;)

Owen Pierce · 09-25-2008, 07:00 PM

Shouldn't this whole thing been in the webmaster forum anyways? heheh

Owen Pierce · 09-25-2008, 07:08 PM

Anyways.. COMKAT, there are like 3 solutions in here...

pick one of them.
say thanks.
close the thread.

Phil · 09-26-2008, 09:41 AM

Ok, figured it out. The reason insert ignore didn't work is because when I did export into sql file through phpMyAdmin, it added extra line with ; in it ... don't know why.
so thank you, and its closed.

react · 09-26-2008, 11:41 AM

Forget the export..

insert ignore into table2 select * from table1;

or if you have a disparate structure:

insert ignore into table2 (some, fields, here) select some, fields, here from table1;

Glad it worked out.

09-24-2008, 03:47 PM	#2
Babaganoosh ♥♥♥ Likes Hugs ♥♥♥ Industry Role: Join Date: Nov 2001 Location: /home Posts: 15,841	If the primary key is unique you could do a regular insert and mysql will just throw an error and continue when it sees a duplicate id. __________________ I like pie.

09-24-2008, 06:29 PM	#3
Phil Confirmed User Join Date: Jan 2004 Posts: 7,659	mysql will error out n that. Im using insert ingnore should work but I'm missing something there. screw it, will do it tomorrow. BTW, why isn't "rollback" command working in MySql? __________________ Ask Phil

09-24-2008, 06:32 PM	#4
GrouchyAdmin Now choke yourself! Industry Role: Join Date: Apr 2006 Posts: 12,085	select * into db2 from db1 where (id <= 201 and id >= 1000)? __________________ AIM: GrouchyGfy

09-24-2008, 07:09 PM	#5
brandonstills Confirmed User Join Date: Dec 2007 Location: Chatsworth, CA Posts: 1,964	1. Get the list of ids on server #1 2. Combine them into a comma separated list: join(', ', $theList); 3. "SELECT * FROM table WHERE id NOT IN ($theList)" __________________ Brandon Stills Industry and programming veteran [email protected] \| skype: brandonstills \| ICQ #495-171-318

09-24-2008, 07:15 PM	#7
gornyhuy Chafed. Join Date: May 2002 Location: Face Down in Pussy Posts: 18,041	http://bogdan.org.ua/2007/10/18/mysq...ts-syntax.html __________________ icq:159548293

09-24-2008, 03:44 PM	#1
Phil Confirmed User Join Date: Jan 2004 Posts: 7,659	Any SQL gurus in the house? Can you help? I have two identical structure DBs. One has 1000+ records (DB1) the other one that has 200 (DB2). I would like to combine those two by adding 1000 records to smaller DB skipping duplicates (based on primary key - ID).. so basically moving 800 records starting with ID=201 from DB1 to DB2 __________________ Ask Phil

09-24-2008, 07:20 PM	#9
sarettah see you later, I'm gone Industry Role: Join Date: Oct 2002 Posts: 14,127	INSERT INTO db2 SELECT * FROM db1 WHERE id NOT IN (SELECT id FROM db2) __________________ All cookies cleared!

09-24-2008, 07:23 PM	#10
Lane Will code for food... Join Date: Apr 2001 Location: Buckeye, AZ Posts: 8,496	why didn't "insert ignore" work? __________________

09-24-2008, 07:24 PM	#11
mikesouth Confirmed User Industry Role: Join Date: Jun 2003 Location: My High Horse Posts: 6,334	im not sure I see the problem a simple unload of one database to a file then insert it into the other. If you want more control unload both to flat files, merge and sort (UNIX) then and use the uniq command(UNIX) to dedupe the merged file then load it into a database __________________ Mike South It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.

09-24-2008, 10:16 PM	#13
Owen Pierce Registered User Join Date: Dec 2007 Posts: 95	uhm... you said "DB1" and "DB2".. so.. I'm gonna assume you actually mean databases, although it seems more likely you mean tables... anyways. INSERT INTO db1.table (field1,field2,field3) SELECT t2.field1, t2.field2, t2.field3 FROM db1.table t2 LEFT OUTER JOIN db2.table t1 ON (t1.id = t2.id) WHERE t1.id IS NULL

09-24-2008, 11:20 PM	#17
mrkris Confirmed User Join Date: May 2005 Posts: 2,737	You all make shit way to complex. __________________ PHP-MySQL-Rails \| ICQ: 342500546

09-25-2008, 12:02 AM	#20
Phil Confirmed User Join Date: Jan 2004 Posts: 7,659	I had 4 beers tonight, so Im not touching it until tomorrow, but please tell me why "rollback" isn't working for MySql. Is it oracle only feature? does it do commit on it own??? __________________ Ask Phil Last edited by Phil; 09-25-2008 at 12:04 AM..

09-25-2008, 12:09 AM	#22
PPC Registered User Join Date: Jun 2007 Posts: 60	export both into excel and select all data then goto toolbar Data > Filter > Advanced Filter click copy to another location change the copy to range ... click unique records only then hit ok but hey, I'm no SQL guru :p (btw this will only work for up to like 65k, excels row limit) __________________ Pay Per Click Last edited by PPC; 09-25-2008 at 12:10 AM..

09-25-2008, 12:13 AM	#24
GrouchyAdmin Now choke yourself! Industry Role: Join Date: Apr 2006 Posts: 12,085	Or, ya know, you could just take my query and insert the field/values but NOT the auto_increment key field for X records. But, hey, I haven't seen so many convoluted ways to do database transactions OUTSIDE of a database in.. forever. __________________ AIM: GrouchyGfy

09-25-2008, 04:58 AM	#27
V_RocKs Damn Right I Kiss Ass! Industry Role: Join Date: Dec 2003 Location: Cowtown, USA Posts: 32,422	Or just select all from the 1000 and select all from 200 where id = id and if it don't match, insert

09-25-2008, 07:51 AM	#29
grumpy Too lazy to set a custom title Join Date: Jan 2002 Location: Holland Posts: 9,870	some real geniuses here __________________ Don't let greediness blur your vision \| You gotta let some shit slide icq - 441-456-888

09-25-2008, 10:21 AM	#35
Varius Confirmed User Industry Role: Join Date: Jun 2004 Location: New York, NY Posts: 6,890	You could always use INSERT IGNORE also, to just try and insert them all from db1 and it will error on any duplicates (primary key), BUT keep going and not stop if you are using INSERT IGNORE __________________ Skype variuscr - Email varius AT gmail

09-25-2008, 10:34 AM	#36
react Confirmed User Industry Role: Join Date: Sep 2003 Location: NZ Posts: 673	Yes, stop fucking stringing us along and tell us why INSERT IGNORE is not an option. __________________ -- react

09-25-2008, 07:00 PM	#39
Owen Pierce Registered User Join Date: Dec 2007 Posts: 95	Shouldn't this whole thing been in the webmaster forum anyways? heheh

09-25-2008, 07:08 PM	#40
Owen Pierce Registered User Join Date: Dec 2007 Posts: 95	Anyways.. COMKAT, there are like 3 solutions in here... pick one of them. say thanks. close the thread.

09-26-2008, 09:41 AM	#41
Phil Confirmed User Join Date: Jan 2004 Posts: 7,659	Ok, figured it out. The reason insert ignore didn't work is because when I did export into sql file through phpMyAdmin, it added extra line with ; in it ... don't know why. so thank you, and its closed. __________________ Ask Phil

09-26-2008, 11:41 AM	#42
react Confirmed User Industry Role: Join Date: Sep 2003 Location: NZ Posts: 673	Forget the export.. insert ignore into table2 select * from table1; or if you have a disparate structure: insert ignore into table2 (some, fields, here) select some, fields, here from table1; Glad it worked out. __________________ -- react