Solved

Insert/Update from another table

Posted on 2014-01-27
8
652 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
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

634 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