Insert/Update from another table

Don't know if this is really possible
I am trying to concatenate three fields from one table and insert into another table, if the key exists then just update the value.  But I want this to happen for all rows in the source table
What I have so far

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)

Open in new window

LVL 58
GaryAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chaauConnect With a Mentor Commented:
I think all you need to do is to fix string quotes, to use single quotes:
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

Open in new window

Also, please note that this will only work if you have a UNIQUE index on your "sku" column. Otherwise, ON DUPLICATE KEY UPDATE will not work
0
 
Terry WoodsIT GuruCommented:
Yes, it's possible; I just tested it and it worked.

Your query looks good; have you tried it? Note that the duplicate key part of the query will only be triggered when a primary key or unique index is violated by the insert though.
0
 
GaryAuthor Commented:
@ chaau
Really!!!

@Terry
It works but only updates one row in the target table.
It should be taking all rows (concatenated fields) from the source table (import) and adding/updating them in the target table (product_images)

Maybe this would be easier to just separate into an UPDATE query and then do an INSERT query for any missing rows.  Thought it would be possible with a single INSERT/UPDATE query.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Terry WoodsConnect With a Mentor IT GuruCommented:
Are you sure you don't have a filter on the select that's reducing the number of rows that are operated on?

Are any new rows being inserted as well?
0
 
GaryAuthor Commented:
Ah jaysus - I'm an idiot!
I had a unique key set on a column that I had just abandoned using so it ended up it was updating the same single row with the ON DUPLICATE KEY UPDATE
0
 
GaryAuthor Commented:
Although not the answer it led me to double check the keys on the table for some reason that only my brain knows.
0
 
chaauCommented:
Have a look at this SQL Fiddle. Notice, that although the source table (tab2) contains 4 rows, the target table will be populated with three records only. This happens because the source table has duplicate records on its own (records with ID=2). Maybe you have a similar situation?
    create table tab1 (sku int not null primary key,
     descr varchar(100) null);
    
    insert into tab1 values(1, '1,2,3');
    insert into tab1 values(2, '1,2,3');
    
    create table tab2 (id int,
     item1 varchar(10),
     item2 varchar(10),
     item3 varchar(10));
    insert into tab2 values (1, '2', '3', '4');
    insert into tab2 values (2, '5', '6', '7');
    insert into tab2 values (3, '7', '6', '7');
    insert into tab2 values (2, '9', '6', '7');
    
    INSERT INTO tab1 (descr, sku)
    select concat_ws(',',item1,item2,item3),id
    from tab2
    ON DUPLICATE KEY UPDATE descr=concat_ws(',',item1,item2,item3);

**Query 1**:

    
    select * from tab1

**[Results][2]**:
    
    | SKU | DESCR |
    |-----|-------|
    |   1 | 2,3,4 |
    |   2 | 9,6,7 |
    |   3 | 7,6,7 |

Open in new window

0
 
GaryAuthor Commented:
I've decided to split the points.
@chaau
Even though your comment about using apostrophes is a Really statement you mentioned unique indices so that should have triggered my thought processes.
@Terry
That was the nudge I needed to double check my table

Thanks
0
All Courses

From novice to tech pro — start learning today.