How to merge MySQL tables?

I have 2 MySQL tables that I want to merge together where a CAS number in one matches the other. Each table has some overlapping info, some unique.

I want to merge table1 into table2 where the CAS Number matches, but only UPDATE the fields that are empty (I don't want to replace info already there)

If CAS number does not exist in table2, I want to INSERT this info (I'm assuming the insert will autonumber my IDs as normal, which is what I want).

Anyone know how to accomplish this?

 

Something like this:

 

I was looking to do the same... ill be sure to try this method

thanks

 

Thank you, that code works.

2 questions:

AND t1.col1 IS NULL OR t1.col1 = ''

Code (markup):

1. Is the above redundant? Or does NULL not include '' and '' does not include NULL?

2. Can the update part of the code be modified to update multiple fields at once? As is, I would have to modify and run the SQL code 30 or more times to update all the fields and surely there's an easier way? (they all use the same common id)

Thanks!

 

1) NULL is ... just NULL. You can't compare it, that is why the expression is col1 IS NOT NULL. You cant check for col1 = NULL or col1 != NULL.

2) You can write something like this:

The real problem here is with the WHERE clause. This way you append more conditions using OR.

 

So, the part: t1.col1 = '' ...will not return NULL values, then? So, anytime I query MySQL not wanting blank/empty records, I need to do both: t1.col1 IS NULL OR t1.col1 = ''

 

Dude, if col1 is PRIMARY KEY - it can't be NULL So the WHERE clause is not needed.

 

The patch is untested, but should work on a new Drupal install. If your mysql install supports the MERGE engine, then you will have the two tables created for watchdog and accesslog log events. Inserts will go into the _insert table but will be selected from the merged log tables.