Mysql update only one line field

Asked

Viewed 309 times

-1

In Myssql I have a table like this:

ID

I receive in a PHP via POST id of the table and which photo I must clean from the database:

id= 1
foto = aaa.jpg

With UPDATE need to leave the table like this:

inserir a descrição da imagem aqui

That is, just clear the table field with id 1, where the value of the photo is equal to aaa.jpg. How to do it? The difficulty lies in knowing if the aaa.jpg photo is in the column FOTO1, FOTO2 or FOTO3

I believe it is with IF inside SET, something like this I imagine to be the way:

UPDATE tabela SET foto1 = '' IF(foto1='aaa.jpg'), foto2 = '' IF(foto2='aaa.jpg'), foto3 = '' IF(foto3='aaa.jpg')
WHERE id = '1'
mysql sql-update

  • Take a look in that question from SOEN, can help you

  • https://pastebin.com/XPsre3Yv

  • Hard to understand what you need... but let’s try!

  • @Valdeirpsr perfect! That was it.

  • Wouldn’t it be one of the solutions to your problem to create an n-table, multi-valued attribute? So you could search in the table by the name of the photo... Or even name your columns according to usage. Note: Now I understood what I wanted to ask kkk

1 answer

2


Try it like this: 

UPDATE 
   fotos
SET 
   FOTO1 = IF(FOTO1='aaa.jpg', NULL, FOTO1),
   FOTO2 = IF(FOTO2='aaa.jpg', NULL, FOTO2),
   FOTO3 = IF(FOTO3='aaa.jpg', NULL, FOTO3),
WHERE 
   'aaa.jpg' IN (FOTO1 , FOTO2, FOTO3) AND ID = 1

source: https://stackoverflow.com/q/46093439/7437072

Or as suggested in the comments by Valdeir:

UPDATE
  `fotos`
SET
  `foto1` = REPLACE(`foto1`, 'aaa.jpg', ''),
  `foto2` = REPLACE(`foto2`, 'aaa.jpg', ''),
  `foto3` = REPLACE(`foto3`, 'aaa.jpg', '')
WHERE
  id = 1

Browser other questions tagged mysql sql-update

You are not signed in. Login or sign up in order to post.