CODEWITHSUNDEEP
sqlfirebird
IElite
IElite

Reputation: 1848

Help with Update SQL

I have two tables (Master, Responses)

I need use the Master_Id field from the Responses table, to look it up in Master table (id) for matching record and update a date field in the Master table with a date field in the Responses table

Master Table's Id field links to Reponses table's Master_id field

Can this be done in SQL ????

UPDATE Master m SET m.date = (
     SELECT r.date FROM Reponses r WHERE r.master_id = m.id)  
WHERE m.id IN (SELECT master_id FROM Responses)

I get a - General SQL Error - Multiple rows in singleton select.

Do i need to add a join or change something?

Upvotes: 0

Views: 6505

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

You can try FIRST

UPDATE Master m SET m.date = (
     SELECT FIRST 1 r.date FROM Responses r WHERE r.master_id = m.id)  
WHERE m.id IN (SELECT master_id FROM Responses)

Upvotes: 1

jachguate
jachguate

Reputation: 17203

The error means there are some cases where one particular master record have more than one Response, the sub-query to get the date returns more than one row. As you might know, the server does not have the logic to pick the correct response and update the master table (the dates may be different or not, who knows), so shows the error message.

What you have to do is to ensure the sub query returns only one row: the exact one row you require to pick the response date on the master table. If all response rows contains the same date, you can use the FIRST clause to return just the first row, as suggested by @cyberkiwi.

But you may want to pick the newest, the oldest or any other based on a criteria:

All this will suppress the error message, the correct one may not be sown, depending on your needs:

UPDATE Master m SET m.date = (
     SELECT FIRST 1 r.date FROM Reponses r WHERE r.master_id = m.id)  
WHERE m.id IN (SELECT master_id FROM Responses) 

UPDATE Master m SET m.date = (
     SELECT MAX(r.date) FROM Reponses r WHERE r.master_id = m.id)  
WHERE m.id IN (SELECT master_id FROM Responses) 

UPDATE Master m SET m.date = (
     SELECT MIN(r.date) FROM Reponses r WHERE r.master_id = m.id)  
WHERE m.id IN (SELECT master_id FROM Responses) 

UPDATE Master m SET m.date = (
     SELECT r.date FROM Reponses r WHERE r.master_id = m.id AND r.accepted = 1)  
WHERE m.id IN (SELECT master_id FROM Responses)

Upvotes: 3

Related Questions