CODEWITHSUNDEEP
mysqlsql
Mathieu
Mathieu

Reputation: 53

UPDATE table on JOIN with duplicate values is only returning first value

When I try to import values from one table to another table, I don't get all the results. Example, see code below, where I ask to:

JOIN transaction ON transaction.invoice = invoicing.invoice

Sometimes, I have 2 or 3 invoices with the same invoice number and the UPDATE only happens on the first invoice number, skips the others with same invoice number.

I need all invoices, including duplicates.

UPDATE invoicing
JOIN transaction ON transaction.invoice = invoicing.invoice
SET invoicing.raison = transaction.transaction, invoicing.description = 
transaction.description, invoicing.voyage_passenger = transaction.voyage_passenger

What am I missing?

EDIT:

Table Transaction:

|num    |date           |invoice    |type   |transaction    |amount   |description  
|886051 |1375381803     |859532     |A      |carte          |7.00     |bla bla desc1
|886052 |1375381809     |859532     |P      |repro          |0.00     |bla bla desc2

Table Invoicing - has approx 8 more columns than table above not noted below. Again, I just need the info from the 2nd row above to also be included into the table below in a new row. I also realize now that I should need an INSERT rather than UPDATE to insert these duplicate rows.

|num    |date           |invoice    |type   |raison |amount   |description   |clientID
|951100 |1375381803     |859532     |A      |carte  |7.00     |bla bla desc1 |121212
|951101 |1375401111     |859533     |A      |carte  |7.00     |bla different |222444

Thank you for your help.

Upvotes: 1

Views: 147

Answers (2)

Mihai
Mihai

Reputation: 26784

The invoice number in the first table in the join probably doesn't exist in the second table.Why not a LEFT JOIN?

Upvotes: 0

valex
valex

Reputation: 24144

Don't know what to do with ClientId. According your sample data invoice can be in different dates so I'm not sure if ClientId is the same for the different dates invoice.

insert into invoicing (`num`, `date`, `invoice`, `type`, 
                       `raison`, `amount`, `description`)
select `num`, `date`, `invoice`, `type`, `transaction`, `amount`, `description`
from Transaction t
where not exists
( select `num` from invoicing i
  where 
       i.`num`=t.`num`
   and i.`date`=t.`date`
   and i.`invoice`=t.`invoice`
   and i.`type`=t.`type` 
   and i.`raison`=t.`transaction` 
   and i.`amount`=t.`amount`  
   and i.`description`=t.`description`  

 )

SQLFiddle demo

Upvotes: 1

Related Questions