Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

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

0
Gary
Asked:
Gary
  • 4
  • 2
  • 2
2 Solutions
 
chaauCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Terry WoodsIT 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now