MySQL Inner join using Non unique ID

Question

i want to update my table from another table using inner join,

Query :

UPDATE XYZ INNER JOIN ABC on ABC.Id = XYZ.Id
       SET XYZ.Std = ABC.Std

sample data:

Table XYZ

           ID                                                          STD

401471  QWERTY1     0   13.381  2013-01-01 00:00:00 2002-06-01 00:00:00 0   0   0   2002-06-01 00:00:00 2005-05-31 00:00:00 
401472  QWERTY2     0   13.488  2013-01-04 00:00:00 2002-06-01 00:00:00 0   0   0   2002-06-01 00:00:00 2005-05-31 00:00:00 
401473  QWERTY3     0   13.465  2013-01-07 00:00:00 2002-06-01 00:00:00 0   0   0   2002-06-01 00:00:00 2005-05-31 00:00:00

Table ABC

ID                                              STD Q  W

QWERTY1 2002-06-01 00:00:00 2005-05-31 00:00:00 0   0   0
QWERTY2 2002-07-01 00:00:00 2005-06-30 00:00:00 0   0   0
QWERTY3 2002-08-01 00:00:00 2005-07-31 00:00:00 0   0   0

The ID is not primary key, and i have more than 1.5 millions records with repeated ID and it repeated because the dates are different.

so my query is not working, as the connections gets lost error 2013, i tried changing the timeout from 600 to 6000, but failed to work as i cannot change the timeout of the db server which is in the cloud.

can anyone tell me , if the query is correct, if yes why is the connection getting lost?

so can i do it in chunks if the query is correct?

score 0 · Answer 1 · answered Jul 27 '17 at 07:18

0

Try this -

UPDATE XYZ
set XYZ.std = ABC.std
from XYZ inner join ABC on XYZ.id = ABC.id

answered Jul 27 '17 at 07:18

Santosh Paswan

84
5

score 0 · Answer 2 · answered Jul 27 '17 at 07:22

0

Query might be taking too long to execute that's why you'll be having timeout. You can do it in chunks by setting the range in your query like:

UPDATE XYZ 
    INNER JOIN ABC on ABC.Id = XYZ.Id
    SET XYZ.Std = ABC.Std
    WHERE ABC.Id between 1 AND 50000;

You can use any other parameter to create chunks, above query is just an example.

answered Jul 27 '17 at 07:22

Shaharyar

12,254
4
46
66

by my id is alphanumeric, so i cant set a range. – Sriram Arvind Lakshmanakumar Jul 27 '17 at 08:05
you don't have any integer based `PK` in the table? – Shaharyar Jul 27 '17 at 08:06
yes there is AI id , but but 1.5million rows, how much range can set? – Sriram Arvind Lakshmanakumar Jul 27 '17 at 08:21
can i do this, suppose date is unique for each id, so i can write my query as UPDATE XYZ INNER JOIN ABC on ABC.Id = XYZ.Id and ABC.date=XYZ.date SET XYZ.Std = ABC.Std – Sriram Arvind Lakshmanakumar Jul 27 '17 at 08:23
Yes you can do this, but still you'll have to set the range to create chunks. It is easy, like I just did with `where` clause. Put range on id `WHERE ABC.Id between 1 AND 50000`, in 2nd query `50001 AND 100000` and so on.. – Shaharyar Jul 27 '17 at 08:52

score 0 · Answer 3 · answered Jul 27 '17 at 07:25

0

You can do:

UPDATE XYZ INNER JOIN ABC on ABC.Id = XYZ.Id
       SET XYZ.Std = ABC.Std;

in MySQL (as desicribed here) but it's not native ANSI SQL

Do you have set an index on the id column? You don't have to make it primary but there should be an index.

answered Jul 27 '17 at 07:25

Ivo Gutleben

1

my id is alphanumeric and not unique so can i set the index ? it is not possible i suppose. – Sriram Arvind Lakshmanakumar Jul 27 '17 at 08:26
you can make following if you have MyISAM or InnoDB create fulltext index [INDEXNAME] on [TABLENAME] ([COLUMNNAME]); – Ivo Gutleben Jul 31 '17 at 16:55
CREATE fulltext INDEX RbwIndex ON HistoricalRR (Id); did this, after that i ran the join query , still after 30 secs it expires. and should i set index of id in both the tables? – Sriram Arvind Lakshmanakumar Aug 01 '17 at 04:59
Yeah you have to set this on both id columns – Ivo Gutleben Aug 02 '17 at 11:51

MySQL Inner join using Non unique ID

3 Answers3