Solved

Insert/Update from another table

Posted on 2014-01-27
8
651 Views
Last Modified: 2014-01-27
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
Comment
Question by:Gary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 250 total points
ID: 39813887
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39813890
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
 
LVL 58

Author Comment

by:Gary
ID: 39813903
@ 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 250 total points
ID: 39813909
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
 
LVL 58

Author Comment

by:Gary
ID: 39813921
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
 
LVL 58

Author Comment

by:Gary
ID: 39813922
Although not the answer it led me to double check the keys on the table for some reason that only my brain knows.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39813924
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
 
LVL 58

Author Comment

by:Gary
ID: 39813929
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

Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question