Solved

mysql sql statement - SQL INSERT INTO SELECT

Posted on 2016-10-20
11
59 Views
Last Modified: 2016-10-20
I have a table named table1 which has the names of customers inside.
In table1 and table2 I have unique customer id which I can use when I copy customer names,

I want to copy all the names from table1 into table2 with the related customer id?

how should I write the sql statement?
0
Comment
Question by:Braveheartli
  • 4
  • 4
  • 3
11 Comments
 
LVL 1

Author Comment

by:Braveheartli
ID: 41851586
I have a unique id on the both table : customerid
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41851588
use Update if the customer Id already exists in table2

use Insert into if the customer id not exists in table2
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 41851592
something like this should do if and only if your customer_id field is unique in the destination table2

insert into table2 select customer_id, customer_name from table1 on duplicate key update customer_name = VALUES (customer_name)

Open in new window


---

if you don't have the index, try something like this

lock database

create table temp as select table2.*,table1.customer_name from table2 inner join table1 on table1.customer_id =  table2.customer_id

rename table2 to t , temp to table2

drop table t

Open in new window

0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41851595
for update:

Update table2 a
inner join table1 b
on a. customerid = b.customerid
set a.customername = b.customername

Open in new window


for insert:

Insert into table2 (customerid, customername)
select a.customerid, a.customername
from table1 a left join table2 b
on a.customerid = b.customerid
where b.customerid is null

Open in new window


code not tested, pls customize accordingly.
0
 
LVL 1

Author Comment

by:Braveheartli
ID: 41851612
Dear Ryan Chong,
what does a mean? I mean what is table2 a?
a = column?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41851617
nope, a is the alias of the table.

we can write:
 table2 a

Open in new window

as
 table2 as a

Open in new window

0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 41851632
did you try using the "on duplicate key update" way ? unless there is something not workable, it is the only way to do it in a single query
0
 
LVL 1

Author Comment

by:Braveheartli
ID: 41851639
Dear skullnobrains, Ryan Chong

I'm sorry, I couldn't manage it..

My actual table name is "bayibilgileriiki" which has the data of the customer names.
and the a column name is "adi"

I want to copy "adi" coloumn to "KullaniciBilgileriiki" table's "isim" column.
"isim" column is now NULL.

I have unique customer id on both tables which coloumn name is "uid" for both of them

could you help me please?
0
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41851649
you should able to do an update like this?

Update KullaniciBilgileriiki a
inner join bayibilgileriiki b
on a.uid= b.uid
set a.isim = b.adi

Open in new window

let me know if you got any syntax error, etc
0
 
LVL 1

Author Closing Comment

by:Braveheartli
ID: 41851653
thank you
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 41851664
just for reference since you already got a working solution

insert into KullaniciBilgileriiki ( uid , isim ) select uid,adi from bayibilgileriiki on duplicate key update isim = VALUES (isim)
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article discusses how to create an extensible mechanism for linked drop downs.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now