Perform INSERT INTO query with SELECT DISTINCT with data from 2 tables

Question

Perform INSERT INTO query with SELECT DISTINCT with data from 2 tables

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (1 votes)
#4 by (1 votes)
#5 by (0 votes)
#6 by (0 votes)

5

I need to insert values in a table by SQL query that selects the different id_cliente that exist in the table Clientes with its amount and the rest of the fields in the table Facturas adding 1 to field num_factura (is the invoice number of the client itself).

The idea is to do this every month to generate the monthly invoices of the clients and I want them to be stored in the Invoices table.

These are the tables and their respective fields:

Invoices

bill_id (Key)
customer_id
num_factura
date_fact
amount

Customers

client_id (key)
total_price

The id_factura field is a unique and self-reinforcing field.

The id_cliente field can be repeated for each different num_factura .

The date_data field is provided by me every month.

I've tried this and it duplicates all the values in the Facturas table:

INSERT INTO Facturas ( id_cliente, num_factura, fecha_factura, importe )
SELECT Clientes.Id_cliente, Facturas.num_factura + 1, Facturas.fecha_factura, 
Clientes.precio_total
FROM Clientes 
INNER JOIN Facturas ON Clientes.Id_cliente = Facturas.id_cliente
WHERE Clientes.Id_cliente IN (SELECT DISTINCT Id_cliente FROM Clientes);

When I perform the query for the first time it executes correctly but the next time the execution repeats everything that existed in the table Invoices and also the new also, going from 100 records, to 200, 400, 800, etcetera.

How can this situation be solved?

Example of what happens when executing:

Original Table Invoices:

id_factura   id_cliente  num_factura  fecha_factura  importe
         1           10         001     31/08/2016    105€
         2           11          001     31/08/2016     95€

Table Invoices after the first INSERT execution

id_factura   id_cliente  num_factura  fecha_factura  importe
         1           10          001     31/08/2016    105€
         2           11          001     31/08/2016     95€
         3           10          002     31/08/2016    105€
         4           11          002     31/08/2016     95€

Table Invoices after second INSERT execution

id_factura   id_cliente  num_factura  fecha_factura  importe
         1           10          001     31/08/2016    105€
         2           11          001     31/08/2016     95€
         3           10          002     31/08/2016    105€
         4           11          002     31/08/2016     95€
         5           10          002     31/08/2016    105€
         6           11          002     31/08/2016     95€
         7           10          003     31/08/2016    105€
         8           11          003     31/08/2016     95€

What I want to get after the second consultation ...:

    id_factura   id_cliente  num_factura  fecha_factura  importe
             1           10          001     31/08/2016    105€
             2           11          001     31/08/2016     95€
             3           10          002     31/08/2016    105€
             4           11          002     31/08/2016     95€
             5           10          003     31/08/2016    105€
             6           11          003     31/08/2016     95€

sql

asked by Mariano 11.08.2016 в 14:09

source

6 answers

I do not understand the use of std :: setw Change color of an image with css

score 3 · Answer 1

Try this:

INSERT INTO Facturas ( id_cliente, num_factura, fecha_factura, importe )
SELECT c.id_cliente,
(
  SELECT max(num_factura) + 1 from facturas
  WHERE id_cliente = c.id_cliente
) as num_factura, -- obtiene el ultimo numero de factura por cliente y le suma 1
current_date as fecha_factura,
c.precio_total
FROM Clientes c;

Only modify the current_date by the date that corresponds to the invoice.

score 1 · Answer 2

One solution that worked for me is executing your same query but inserting the data in another table that contains exactly the same fields as the Invoices table.

Example:

INSERT INTO Facturas_copy ( id_cliente, num_factura, fecha_factura, importe )
SELECT Clientes.Id_cliente, Facturas.num_factura + 1, Facturas.fecha_factura, 
Clientes.precio_total
FROM Clientes 
INNER JOIN Facturas ON Clientes.Id_cliente = Facturas.id_cliente
WHERE Clientes.Id_cliente IN (SELECT DISTINCT Id_cliente FROM Clientes);

score 1 · Answer 3

Try using a temporary table:

SELECT 
 Clientes.Id_cliente, 
 Facturas.num_factura + 1, 
 Facturas.fecha_factura, 
 Clientes.precio_total
INTO #temp
FROM Facturas 
INNER JOIN Clientes ON Clientes.Id_cliente = Facturas.id_cliente

INSERT INTO Facturas 
( id_cliente, num_factura, fecha_factura, importe )
SELECT 
 Id_cliente, 
 num_factura, 
 fecha_factura, 
 precio_total
FROM #temp

score 1 · Answer 4

I still can not comment on your answers due to lack of points.

In the end the answer that has solved the problem is that of @Dan and I only had to modify the current_date by entering my data to enter the date_data.

Link to the valid @Dan response: link

I leave you the final code for the help of others.

INSERT INTO Facturas ( id_cliente, num_factura, fecha_factura, importe )
SELECT c.id_cliente, (SELECT max(num_factura) + 1 from facturas
  WHERE id_cliente = c.id_cliente
) AS num_factura, ([Formularios]![Fecha Facturación]![fecha_facturas]) AS fecha_factura, c.precio_total
FROM Clientes AS c;

[Billing Date] = Name of the Form.

[date_factures] = Name of the text box where we enter the date for which we want to generate customer invoices.

Thanks to everyone.

score 0 · Answer 5

Try this:

INSERT INTO Facturas ( id_cliente, num_factura, fecha_factura, importe )
SELECT Clientes.Id_cliente, MAX(Facturas.num_factura) + 1, Facturas.fecha_factura, Clientes.precio_total
FROM Clientes 
INNER JOIN Facturas ON Clientes.Id_cliente = Facturas.id_cliente
GROUP BY Facturas.Id_cliente;

score 0 · Answer 6

From what I understand, you want to insert an invoice for each client, using the information of date and number of a previous invoice to the same client.

For that case, the solution is this:

INSERT INTO Facturas ( id_cliente, num_factura, fecha_factura, importe )
SELECT   Clientes.Id_cliente
       , max(Facturas.num_factura) + 1
       , max(Facturas.fecha_factura)
       , Clientes.precio_total
  FROM Clientes 
       INNER JOIN Facturas ON Clientes.Id_cliente = Facturas.id_cliente;

Of course, here, if you enter a new customer, who has no invoice, no a new invoice will be created for him. In the case that if you want to create invoices for new clients, you must use this other version of the script and provide a default value for the invoice number and date, for example:

INSERT INTO Facturas ( id_cliente, num_factura, fecha_factura, importe )
SELECT   Clientes.Id_cliente
       , coalesce(max(Facturas.num_factura) + 1, 1)
       , coalesce(max(Facturas.fecha_factura), '2016-08-01')
       , Clientes.precio_total
  FROM Clientes 
       INNER JOIN Facturas ON Clientes.Id_cliente = Facturas.id_cliente;

Since you do not indicate the engine you use, I used the date constant in ISO format, but that is not well received by all the engines, and sometimes it will depend on the local configuration (of the client, or the server) to its correct operation. It is your task to adapt it to work with the engine of your choice.