I’m a beginner in SQL. I need to build an Insert that will validate some fields of the source table and fill in the target table

Asked

Viewed 59 times

-3

I know the situation doesn’t make much sense, but the important thing for me is to understand how to "copy and paste" from one table to another according to one condition.

The tables are as follows::

Products Products
ID ID
Name Name
V_total V_total
V_ISS V_ISS
V_aliquot V_aliquot
Creation date Creation date
Validation date

All of the Products Table_temp will be inserted in the Products table. What changes is only the Data_validation field.

This is the validation: V_ISS = (V_total * V_aliquota) / 100

If V_ISS is equal to (V_total * V_aliquota) / 100, Copy the date of the Data_creation field of the source table and repeat in the Data_validation field of the target table.

If V_ISS is different from (V_total * V_aliquota) / 100, leave the Data_validation field as NULL.

I tried to do this validation using CASE WHEN but I could not. I only know how to do the direct Insert. Who can help me, thanks so much !!

sql oracle

  • "is to understand how to "copy and paste" from one table to another" first select from the table from where you want to copy back the data you want, then put isse select together with an Insert: insert into produtos (campos) select campos from Produtos_temp where sua condição

  • Explain the problem better, is it a point solution or will it work like this in production ? When is this insertion ? In theory a TRIGGER could do the validation and insertion but the subject still seems to me "gaseous".

2 answers

0

In this case just use the merge.

merge into Produtos p using (Produtos_temp temp) on (temp.V_ISS = temp.V_total * temp.V_Aliquota /100)
when matched then update set p.Data_Validacao = null
when not matched then insert (temp.id, temp.Nome, temp.V_total, temp.V_ISS, temp.V_Aliquota, temp.Data_Criacao) values
(p.id, p.Nome, p.V_total, p.V_ISS, p.V_Aliquota, p.Data_Criacao)

-3

Selects before inserting. In select you validate the records to be inserted, and then do the Insert

Browser other questions tagged sql oracle

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