asked on

Concat and insert if not blank

I have the following which works fine

INSERT INTO product_images (product_image_name,sku)
select concat_ws(';',image1,image2,image3),item from import
ON DUPLICATE KEY UPDATE product_image_name=concat_ws(';',image1,image2,image3)

But I want to add some conditions:
If either image1,image2 or image3 is blank (will be blank and not NULL) then don't include that particular field.
If all fields are blank then skip

Is it possible? Would this make the sql very complicated? I can live with inserting 'blank' fields (as I will just do a delete on them) but would really like the empty fields to be skipped.

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gary

ASKER

That kinda works but it isn't pulling rows where for example image1 does have a value but image2 and image3 don't.
Presumably because you are conditioning that all cols must have a value
from import WHERE NULLIF(image1,'') IS NOT NULL AND
NULLIF(image2,'') IS NOT NULL AND
NULLIF(image3,'') IS NOT NULL

...where I just want skip the fields if they are blank.
I can live with it and do a delete on the blank fields by removing the WHERE condition unless you have a better solution.

SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arnold

I am unclear what it is you are looking to do.
I think it will be more efficient to leave it as you have it.
image1;image2;image3;

if you make the concat conditional (other than complicating the query) you could have results
;;;
image1;;image3
versus
image1;image3
image3
image2;image3

Presumably there is a significance placed on that order when you decode/split the data from the column based on ';'

Jagadishwor Dulal

I think you are almost at the point write a function to check the condition and change your query as per required. And again if you want to having blank then use default value to blank in your database.

Ray Paseur

Not sure of the overall design, but it feels to me like the product_image_name should be a separate table rather than a variable number of image names in a single column. Just a thought... ~Ray

Gary

ASKER

@chaau
The amendment seems to work fine, will just do a bit more testing to make sure nothing is missing.

Gary

ASKER

Thanks, works fine.