CODEWITHSUNDEEP
mysqldatabasesql-updatesql-insertsql-delete
Sushovan
Sushovan

Reputation: 343

UPDATE/INSERT INTO/DELETE FROM table in MYSQL

I have a table in MYSQL database TABLE1, with columns COLA, COLB, COLC and (COLA,COLB) are composite primary keys. Something like this

-----------------------
|  COLA | COLB | COLC |
-----------------------
|   A   |  B   |  C   |
-----------------------
|   A   |  Q   |  D   |
-----------------------
|   A   |  E   |  J   |
-----------------------
|   B   |  W   |  P   |
-----------------------

Also there is background script which passes data to a java program which should update the table under following conditions :

  1. If new values have any new pair for PRIMARY KEYs then INSERT new row into the table.
  2. If new values have any common pair for PRIMARY KEYs then UPDATE the table.
  3. DELETE all other rows Where COLA value matches with new values.

If new vaues are ('A','B','L'),('A','Y','D'),('A','Q','Z') then it should :

  1. UPDATE 1st and 2nd row.
  2. INSERT a new row ('A','Y','D').
  3. DELETE only 3rd row.

So table should look like 

-----------------------
|  COLA | COLB | COLC |
-----------------------
|   A   |  B   |  L   |
-----------------------
|   A   |  Q   |  Z   |
-----------------------
|   B   |  W   |  P   |
-----------------------
|   A   |  Y   |  D   |
-----------------------

To implement this I was running two queries :

INSERT INTO TABLE1 VALUES('A','B','L'),('A','Y','D'),('A','Q','Z') ON DUPLICATE KEY UPDATE COLC=VALUES(COLC);

Which is working the way I want. But when I try to delete other rows I am getting into problem what I am trying is :

DELETE FROM TABLE1 WHERE NOT ((COLA='A' AND COLB='B') OR (COLA='A' AND COLB='Y') OR (COLA='A' AND COLB='Q'));

But it does not work. As it deletes the last row as well. So

  1. How to implement the query?
  2. Can it be clubbed into one query?

THANKS IN ADVANCE :)

Upvotes: 0

Views: 988

Answers (2)

Prince
Prince

Reputation: 496

I also couldn't find one query solution to this issue but for the second query a bit optimised version can be:

DELETE FROM TABLE1 WHERE COLA='A' AND COLB NOT IN ('B','Y','Q');

or 

DELETE FROM TABLE1 WHERE COLA='A' AND COLC NOT IN ('L','Z','D');

Any of the above can be used and it can be a bit scalable than the one you provided.

Upvotes: 1

Sushovan
Sushovan

Reputation: 343

I got the answer to first question. query should be

DELETE FROM TABLE1 WHERE COLA='A' AND NOT ((COLA='A' AND COLB='B') OR (COLA='A' AND COLB='Y') OR (COLA='A' AND COLB='Q'));

Upvotes: 0

Related Questions