CODEWITHSUNDEEP
mysqlsql
M M Mahmudul Hassan
M M Mahmudul Hassan

Reputation: 67

Update table if exist else insert

I have a table named a

userindex bts   userid          accountnumber   isalepo
119459  190900  [email protected]  000000000000004 0
31513   1a1430  [email protected]  000000000000008 0
36981   196100  [email protected]  000000000000018 0
56215   197200  [email protected]  000000000000062 1
145938  199000  [email protected]  000000000000066 0
57410   1a1520  [email protected]  000000000000067 1
38554   198510  [email protected]  000000000000068 0
15595   1a2d10  [email protected]  000000000000069 0
30945   1a1120  [email protected]  000000000000074 0
156180  190320  [email protected]  000000000000081 0

I want to update only bts field if it exist in table b

userindex   bts usage_in_mb
100024  081620  4761.23809814
100044  001320  499.27043915
36981   19f710  1114.07279968
100061  19fb10  10931.33640284
57410   07dc10  108.45470429
100088  164610  1474.35951231
10009   19b700  2920.20797732
100105  19c200  7607.14340212
100110  000610  5771.08287813
156180  194820  1409.85393525

If userindex not exist in table a then i want to insert (userindex, bts,userid ,accountnumber,isalepo) from b and c table. table c contains 

userindex   accountnumber   userid
100024  000000000094190 [email protected]
100044  000000000000018 [email protected]
36981   000000000000067 [email protected]
100061  000000000214989 [email protected]
57410   000000000187084 [email protected]
100088  000000000236716 [email protected]
10009   000000000211229 [email protected]
100105  000000000236222 [email protected]
100110  000000000237036 [email protected]
156180  000000000000081 [email protected]

I am trying this query 

INSERT INTO a
  (
   userindex
  ,bts
  ,userid
  ,accountnumber
  ,isalepo
  ,crestelbts
)
SELECT b.userindex
,b.bts
,c.USERID
,c.accountnumber
,1
,0
FROM b  
left join  c on b.userindex=c.userindex
ON DUPLICATE KEY UPDATE
bts = VALUES(b.bts);

Upvotes: 2

Views: 124

Answers (2)

Amranur Rahman
Amranur Rahman

Reputation: 1123

From the Documentation of MySQL, You can try this:

INSERT INTO a (userindex,bts,userid,accountnumber,isalepo,crestelbts) 
 SELECT b.userindex ,b.bts ,c.USERID ,c.accountnumber ,1 ,0
FROM b left join  c on b.userindex=c.userindex
WHERE NOT EXISTS ((SELECT * FROM b WHERE b.userindex = a.userindex) OR (
   SELECT * FROM c WHERE c.userindex = a.userindex ));

Upvotes: 0

e4c5
e4c5

Reputation: 53734

From the docs

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

This behaviour is because you don't have a unique or primary key. So you need to

ALTER TABLE a ALter table fts add primary key(userindex)

or 

ALTER TABLE a ALter table fts add unique key(userindex)

Upvotes: 1

Related Questions