Updating multiple records to the same ID

Question

Consider the following scenario for controlling license information in a database

Products

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

Suppose the Customer has the 3 solutions

Licence 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 update the license term of all products to a specific customer.

I initially used the following instruction to update all customer 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 customer wishes to update only two licenses in a single action how can I implement this ?

Answer 1

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

Your SQL statement will look 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 products with id = 1 and id =3 of the customer = 11222 will be updated.

I hope I’ve helped!

Answer 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 the name of the product:

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')