CODEWITHSUNDEEP
mysqlsql
Chris Muench
Chris Muench

Reputation: 18318

MYSQL query trim a string based on result of results of another query

I am trying to update a table ONLY if a condition of another table is met. These tables don't have really anything I can join on. (the phppos_app_config is just key/value pairs). The query below does NOT work as I am not allowed to do a count on a subquery.

Here is what I am trying to achieve (doesn't work as it is NOT allowed in sql; but just showing logic)

UPDATE phppos_giftcards SET giftcard_number = TRIM(LEADING ';' FROM giftcard_number) 
WHERE 
COUNT
(
   SELECT value FROM phppos_app_config 
   WHERE `key` = 'disable_giftcard_detection' and `value` = '0'
) =1

Upvotes: 0

Views: 136

Answers (2)

user3104783
user3104783

Reputation: 19

The use of the Exists Clause in the subquery will simplify the sub query and will perform better as unnecessary comparisons will be avoided (assuming that you want to update if there us at least one record in the PHPOS_APP_CONFIG table with the specified key, value)

UPDATE phppos_giftcards SET giftcard_number = TRIM(LEADING ';' FROM giftcard_number) 
WHERE exists
     ( SELECT count(*) FROM phppos_app_config 
       WHERE `key` = 'disable_giftcard_detection' and `value` = '0'
     )

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

I don't have mysql to test so just let me know if it does not work and I will delete 

UPDATE phppos_giftcards SET giftcard_number = TRIM(LEADING ';' FROM giftcard_number) 
WHERE ( SELECT count(*) FROM phppos_app_config 
        WHERE `key` = 'disable_giftcard_detection' and `value` = '0'
      ) = 1

Upvotes: 1

Related Questions