mysql copy data from table to another with the same value?

Question

I'm trying to update my table from another table

I need to update the IP in table1 from the new_IP in table changeip according to the SIM number (whcih is the same on both tables)

I have try to do it from what it say here : mysql update column with value from another table

I get error "IP can't be null"

this is what I wrote in the command line

    UPDATE table1 
SET table1.IP = (
    SELECT changeip.New_IP 
    FROM changeip
    WHERE table1.SIM_NEW = changeip.SIM_Number 
);

what am I doing wrong ?

****************update

this is table1

'10.226.202.169', '8997250000031944123'

'10.226.202.170', '8997250000031944131'

'10.226.202.173', '8997250000031944164'

'10.136.136.101', '8997250400019201597'

'10.136.136.102', '8997250400019201589'

'10.136.136.103', '8997250400019201571'

'10.136.136.104', '8997250400019201563'

and so on........

this is changeip table

'10.226.202.169', '8997250000031944123', '10.136.137.221'

'10.226.202.170', '8997250000031944131', '10.136.137.222'

'10.226.202.173', '8997250000031944164', '10.136.137.223'

'10.226.202.174', '8997250000031944172', '10.136.137.224'

'10.226.202.175', '8997250000031944180', '10.136.137.225'

'10.226.202.177', '8997250000031944206', '10.136.137.226'

Thanks ,

table1.IP must be null, alter the column from not null to null. As changeip.New_IP contains null values — AmmyTech, Aug 08 '18 at 12:55
Seems to be a typo aswell, " WHERE table1.SIM_NEW = hangeip.SIM_Number " — warbio, Aug 08 '18 at 12:58
what should happen if an IP is null in the changeip table? Should it get updated to null or stay with the old IP? — isaace, Aug 08 '18 at 13:10
@Kool-Mind, but table1.IP has a value inside ... I want to overwrite it - all this colom is fill with data . there is no empty cell there. and about what isaace say - can't be null - this is a 100% table with data , no empty\null in the changeip.IP table — David12123, Aug 08 '18 at 14:56

score 0 · Answer 1 · answered Aug 08 '18 at 13:02

0

ALTER table1 MODIFY IP varchar(20) null;

alter the column from not null to null if the other table consist null values

answered Aug 08 '18 at 13:02

AmmyTech

738
4
10

Zaynul Abadin Tuhin · Accepted Answer · 2018-08-09T08:05:57.760

0

you need join and update

 UPDATE table1 t
    inner join changeip p
    on t.SIM_NEW= p.SIM_Number
   and t.IP=p.old_ip
    SET t.IP =p.New_IP

edited Aug 09 '18 at 08:05

answered Aug 08 '18 at 13:11

Zaynul Abadin Tuhin

31,407
5
33
63

when I run this - I don't get any error, but it doesn't update nothing .... – David12123 Aug 08 '18 at 14:53
@David12123 can.you please share both tables data ,because it supposed to be worked – Zaynul Abadin Tuhin Aug 08 '18 at 15:06
tell me how to do it and I will – David12123 Aug 09 '18 at 07:25
@David12123 if you have both tables SIM_Number number it will join and update your table1 – Zaynul Abadin Tuhin Aug 09 '18 at 07:28
do you have multiple New_IP against table1 sim_number? – Zaynul Abadin Tuhin Aug 09 '18 at 07:30
I have update the question with some information and example of my DB - maybe it will help you help me:-) – David12123 Aug 09 '18 at 07:33
@David12123 can you just share with me table1 sample data and changeip tables data – Zaynul Abadin Tuhin Aug 09 '18 at 07:38
@David12123 it will be easy for me to do it if could give those things – Zaynul Abadin Tuhin Aug 09 '18 at 07:39
@David12123 10.226.202.169 this old ip of changeip table right? – Zaynul Abadin Tuhin Aug 09 '18 at 07:52
say that Error Code: 1054. Unknown column 't.SIM_Number' in 'on clause' – David12123 Aug 09 '18 at 08:01
@David12123 does SIM_Number column in your table? – Zaynul Abadin Tuhin Aug 09 '18 at 08:02
@David12123 i missed column name of tabe1 could check plz – Zaynul Abadin Tuhin Aug 09 '18 at 08:06
@David12123 table1 columns name IP,SIM_NEW and changeip columns name old_ip,SIM_Number,New_IP am i right? – Zaynul Abadin Tuhin Aug 09 '18 at 08:08
now it's working ! , Thanks - can you explain to me what was the problem so I will learn for next time ? – David12123 Aug 09 '18 at 08:12
@David12123 actually column name was mistake and for updating two tables you need join two table by using their join keys – Zaynul Abadin Tuhin Aug 09 '18 at 08:13
just to understand - from what I see you make a "rule" to update new IP according to where SIM number is the same (t.SIM_NEW= p.SIM_Number) and also where the old IP is the same (and t.IP=p.old_ip) . whouldn't 1 condition (sim number is the same) would be enough? – David12123 Aug 09 '18 at 08:17

score 0 · Answer 3 · answered Aug 08 '18 at 15:10

0

This should work if you want to update those that have an IP on the changeip table and leaves those that don't have a new IP with the old IP:

UPDATE table1 
SET table1.IP = (
    SELECT changeip.New_IP 
    FROM changeip
    WHERE table1.SIM_NEW = hangeip.SIM_Number 
    AND hangeip.New_IP IS NOT NULL
);

answered Aug 08 '18 at 15:10

isaace

3,336
1
9
22

I get this error : Error Code: 1048. Column 'IP' cannot be null. , which can't be becasue all of table1.IP have data inside - I'm replacing old IP with new IP. – David12123 Aug 09 '18 at 07:21

score 0 · Answer 4 · answered Aug 09 '18 at 10:03

An easy way to achieve the required result is as follows:

Based on your data the start tables are

and

If you run the query

UPDATE table1, changeip 
  SET table1.IP = changeIP.old_IP 
  WHERE table1.SIM = changeip.SIM_Number

The outcome is as follows:

Which I believe is the desired result. Most of the suggestions above are missing the WHERE clause in the UPDATE statement and that is why they fail.

mysql copy data from table to another with the same value?

4 Answers4