SQL Update

[Henry Olive 5]

Interbase;

 

UPDATE PAYROLL P

SET P.CUSTACCRNO=(SELECT C.RNO FROM CUSTACC  C  WHERE C.CUSTNO=P.CUSTNO);

 

I'm getting  multiple rows in singleton select  err.msg.

Thank You

 

[Attila Kovacs 629]

Your subselect (SELECT C.RNO FROM CUSTACC  C  WHERE C.CUSTNO=P.CUSTNO) returning more than 1 rows and the server doesn't know what do you want to do.

 

Either change the subselect to return only 1 row or use an aggregate function in the subselect like SUM() to make one result from the returned rowset.

[Henry Olive 5]

Thank you so much Attila,

If you have time can you please correct my code.

[Attila Kovacs 629]

I'm afraid you are the only one who can do that as we don't know what do you want to do there.

[Henry Olive 5]

PayRoll Table

CustNo...CUSTACCRNO

1                   Null

2                   Null

 

Target Table CUSTACC

RNO   CUSTNO

101            1

102            2

 

Iwant to fill PayRoll's CUSTACCRNO fields according to CUSTACC RNO values.

After UPDATE The result of Payroll table should be

CustNo...CUSTACCRNO

1                    101  

2                    102

[Attila Kovacs 629]

does it runs on IB?

maybe this would give you some hints

 

SELECT RNO, COUNT(*) FROM CUSTACC

GROUP BY RNO

HAVING COUNT(*) > 1

[Henry Olive 5]

Thank you Attila,
 

I changed the SQL like below but still getting multiple rows err.

UPDATE PAYROLL1 P SET P.CUSTACCRNO=(SELECT C.RNO FROM CUSTACC C WHERE C.CUSTNO=P.CUSTNO )
WHERE EXISTS (SELECT 1 FROM CUSTACC C1 WHERE C1.CUSTNO=P.CUSTNO);
 

[Guest]

hi @Henry Olive

 

dont forget about "keywords" on SQL, like: ROWS, FIRST, LAST, etc...

Quote

SELECT <<field>> FROM <<table>> ORDER BY <<col>> ROWS 1   <----- "ROWS"

ROWS n        Returns the first n rows of the result set, or n percent if used with PERCENT
ROWS m TO n   Returns rows m through n, inclusive or the mth to nth percent
ROWS n BY p   Returns every pth row of the first n rows

 

hug

[Guest]

Basic problem; you expect a query to return one row but it does not.

So where is the problem?

 

Run @Attila Kovacs query above. You will find duplicates.

Are those duplicates an error in the data?

 

If YES => add a unique index on that table to guarantee only one value per identifier. Your original query will work. An exception will happen (duplicate) when trying to "corrupt*" the data the next time.

 

if NO => where is the fault? If you want to fill a reference and there are two values for one reference, and this is ok, you need to rethink. Should it be the sum? Should it be the FIRST or LAST (like @emailx45 suggests)? All of these questions, more questions as the first gets answered, and their answers are based on YOUR design (not your choise of RDBMS). Alais! We cannot possibly help you writing your actual query.

 

If you provide code/schema or writeups complete enough we could write that query for you (as long as we do not have to spend 2 days reading through your schema), at least suggest how to move on.

 

IMHO you should NOT do this "trial and error". You add something, it looks like it's working now, and in time a very mean and hidden problem will hit you, hopefully not in production.

 

* This "corruption" is in the "scope" of your original query. Since it will "break" that query.

[Guest]

If you want to SKIP the records that are multiple... ((select count(*) from ...) = 1) there's also an EXSITS operator, also read about ANY and ALL.

But then for multiple records the table will not be updated so how do you know if the reference was missing or "multiple"?

[Guest]

hi @Henry Olive

 

Another observation, as he indicates Interbase with its DB, so, if it were possible to know how the table was defined, etc ..., to get around any problems of data "integrity" between two tables, my suggestion would be to make use of the own Database capacity, that is: Use Interbase's native business rules, or any other Database.

 

In this way, I would use (in a hypothetical case because I don't know the structure of the table) the "cascade effect" for "updates" and "deletes", through the internal events, such as: "before insert", "after insert", "before update", "after update", "before delete", "after delete", that is, using "TRIGGERS" Interbase tables! Read about "updates and cascade effect", "triggers", "level triggers", and "environment vars like "Inserting", "Updating"...

 

some for begin: http://docwiki.embarcadero.com/InterBase/2020/en/Using_the_FOREIGN_KEY_to_Enforce_Referential_Integrity

 

It does not seem far from a "hypothetical" solution to this case.

 

As a general rule, never mix the "should" be done in the "Database" and the "should" be done in the application!

 

Data business rules should be in the "Database", and, only in the last case, an application could determine them. Now imagine the following case: 10 applications making their own business rules targeting the same Database ... have you ever imagined what could happen at the end of this story?

 

hug

Edited March 1, 2021 by Guest