Performing update on several records for the same ID

Question

Performing update on several records for the same ID

Navigation

#1 by (4 votes)
#2 by (2 votes)

1

Consider the following scenario for controlling information about licenses in a database

Products

1 - Solução x 
2 - Solução y
3 - Solução z

Suppose you have all 3 solutions

Licenses table

| ID | CLIENTE_ID | PRODUTO_ID | DATA_VALIDADE_INICIAL | DATA_VALIDADE_FINAL |
| 1  | 11222      |     1      |    2015-01-01         |   2016-01-01        |
| 2  | 11222      |     2      |    2015-01-01         |   2016-01-01        |
| 3  | 11222      |     3      |    2015-01-01         |   2016-01-01        |

My goal is to upgrade the license term for all products to a specific customer.

Initially I used the following statement to perform update of all client licenses, see:

UPDATE licenca
SET 
data_validade_inicial= '2016-01-01 00:00:00',
data_validade_final= '2020-01-01 00:00:00'
WHERE cliente_id = 11222

I would like to improve this functionality for example if the client wants to update only two licenses in a single action how can I implement this?

mysql sql sql-update

asked by anonymous 27.01.2016 / 16:30

2 answers

2

UPDATE licenca
SET data_validade_inicial= '2016-01-01 00:00:00',
    data_validade_final= '2020-01-01 00:00:00'
WHERE cliente_id = 11222
    AND produto_id in (1,3)

or if you want by product name:

UPDATE lic
SET lic.data_validade_inicial= '2016-01-01 00:00:00',
    lic.data_validade_final= '2020-01-01 00:00:00'
FROM licenca lic
    INNER JOIN produto pr on lic.produto_id = pr.id
WHERE lic.cliente_id = 11222
    AND pr.nome in ('Solução x', 'Solução z')

27.01.2016 / 16:52

Return only integers Select checked when returning

score 4 · Accepted Answer

Partner, you'll need a checkbox next to each record in your listing to mark the records you want to change.

Your SQL statement looks like this:

UPDATE licenca
SET data_validade_inicial= '2016-01-01 00:00:00',
    data_validade_final= '2020-01-01 00:00:00'
WHERE cliente_id = 11222 AND produto_id IN (1,3)

In this example, the validity of the products with id = 1 and id = 3 of the client = 11222 will be updated.

I hope I have helped!