Learn how to a build a cloud-first strategyRegister Now

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

mysql sql statement - SQL INSERT INTO SELECT

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
Braveheartli
Asked:
Braveheartli
  • 4
  • 4
  • 3
1 Solution
 
BraveheartliMarketingAuthor Commented:
I have a unique id on the both table : customerid
0
 
Ryan ChongCommented:
use Update if the customer Id already exists in table2

use Insert into if the customer id not exists in table2
0
 
skullnobrainsCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Ryan ChongCommented:
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
 
BraveheartliMarketingAuthor Commented:
Dear Ryan Chong,
what does a mean? I mean what is table2 a?
a = column?
0
 
Ryan ChongCommented:
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
 
skullnobrainsCommented:
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
 
BraveheartliMarketingAuthor Commented:
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
 
Ryan ChongCommented:
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
 
BraveheartliMarketingAuthor Commented:
thank you
0
 
skullnobrainsCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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