0

Here's my problem

I have this table called list:

||client||name||address||zone||block||day||document||

and list gets an update for document from another table, payments, from columns named document and client too.

The thing is that when I run this query

UPDATE list SET document=(SELECT document FROM payments WHERE list.client=payments.client)

I get the error Subquery returns more than 1 row when the document value is repeated for different client value on payments table. Obviously I get the error because the value is repeated, but I need to fill the document column with the corresponding clienteven do there's the same document for different client.

How is the query suppossed to be in this case? If it exist, of course.

Thanks.

Cristian Bonilla
  • 1
  • Yes, I know, is a duplicated value, but I mean, I need to set the `document` even do is duplicated for different `client`. – Cristian Bonilla Jul 24 '15 at 23:20
  • The next question is: why are you duplicating this value? Unless you are in the process of migrating the DB structure, such a duplication smells like a flawed design. – RandomSeed Jul 24 '15 at 23:33
  • @RandomSeed The document is a like a receipt or payment document number, but for the company I'm working with they use the same document number for different clients, so they want to create a report table so they can know whats was the last document number the client used. – Cristian Bonilla Jul 24 '15 at 23:40
  • Please explain in natural language what you want to do. I have the feeling that your query is so wrong that it is misleading to us. – RandomSeed Jul 24 '15 at 23:44
  • Ok. Payment table has all the payment that the different clients (or customers) has made. The list table is just to create a report with a PHPbuilder, so, when I go to the PHP webpage for list, the server run a script with the query, but it gives me the error. I know is because there's payments has the same document number for different client. But I need a query to update and set the document number even do there's different client for the same document. – Cristian Bonilla Jul 24 '15 at 23:47
  • Ok sorry I totally missed the last part of your previous comment. The other question (which I marked your question a duplicate of) should help you find the solution to your problem. – RandomSeed Jul 24 '15 at 23:50
  • Note: a [`VIEW`](https://dev.mysql.com/doc/refman/5.6/en/views.html) is probably a better tool for this task than a report table, since a view is always up to date. – RandomSeed Jul 24 '15 at 23:56
  • THANKS. I will try with the other question. – Cristian Bonilla Jul 24 '15 at 23:57
  • Oh and this will probably answer your next question too:http://stackoverflow.com/q/17038193/1446005 ;) – RandomSeed Jul 25 '15 at 00:00

1 Answers1

0

The link by @nomistic is very helpful, you are basically getting all results where the client values match across the list and payments table.

Are there multiple entries in the 'list' table where the client is the same?

If the list table only has one instance of client, you need to filter on the specific payment record (row) which you are updating from, currently you will always get more than one result, your query needs to be structured such that it will return a unique record.

You also didn't put a WHERE clause against the UPDATE query on the list table which could affect multiple rows, did you mean to include one?

Example: 

UPDATE list SET document=(SELECT document FROM payments WHERE list.client=payments.client AND payments.id='1234') WHERE client='ClientX';

If this query is being run in response to an update to another table (i.e. payments) you will want to use the update data (assuming your query returns a new rows ID, for example) you have from the change to the payments table to update the list table.

hughjidette
  • 136
  • 7
  • Sorry, I can't see @nomistic link, do you have it? The multiple entries are for different client. – Cristian Bonilla Jul 24 '15 at 23:38
  • It was this link: [link](http://stackoverflow.com/questions/24386909/subquery-returns-more-than-1-row-solution-for-update-query-using-select-statemen) – hughjidette Jul 25 '15 at 00:54