CODEWITHSUNDEEP
mysqlsql
enano2054
enano2054

Reputation: 329

DELETE FROM table WHERE condition is met in other table

I have this SQL query that has been working great. I would like to have something similar that would delete a line from PRC_FIX when the column DESCR in IM_ITEM begins with Clearance instead of where ITEM_VEND_NO = 'GAMES WORK'. 

DELETE `PRC_FIX` FROM `PRC_FIX` 
INNER JOIN `IM_ITEM` ON `IM_ITEM`.`ITEM_NO` = `PRC_FIX`.`ITEM_NO` 
                     AND `IM_ITEM`.`ITEM_VEND_NO` = 'GAMES WORK'

Thanks for your help.

Edit: This was marked as a possible duplicate. I don't know that looking at the suggested duplicate would have helped me because I wouldn't have known how to implement it in this scenario involving 2 tables, but I'm willing to admit that might be my fault due to me being new to SQL.

Upvotes: 0

Views: 135

Answers (3)

Ali Faris
Ali Faris

Reputation: 18630

DETELE FROM PRC_FIX WHERE ITEM_NO IN (SELECT ITEM_NO FROM IM_ITEM WHERE ITEM_VEND_NO` = 'GAMES WORK')

Upvotes: 2

MercyDude
MercyDude

Reputation: 914

You need to use the wildcard %.

in order to match with this string with different string which begins with "Clearance" you need to use "Clearance%".

Look here: SQL like search string starts with

You're fixed code:

 DELETE `PRC_FIX` FROM `PRC_FIX` 
    INNER JOIN `IM_ITEM` ON `IM_ITEM`.`ITEM_NO` = `PRC_FIX`.`ITEM_NO` 
                         AND IM_ITEM.DESCR LIKE 'Clearance%'

Upvotes: 2

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2073

You can use

DELETE PRC_FIX
  FROM PRC_FIX
 INNER JOIN IM_ITEM
    ON IM_ITEM.ITEM_NO = PRC_FIX.ITEM_NO
 WHERE UPPER(IM_ITEM.DESCR) LIKE 'CLEARANCE%';

Upvotes: 2

Related Questions