Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert/Update from another table

Posted on 2014-01-27
8
Medium Priority
?
655 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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 1000 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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

722 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