Jump to content
Henry Olive

SQL Update

Recommended Posts

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

 

Share this post


Link to post

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.

Share this post


Link to post

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

Share this post


Link to post

does it runs on IB?

maybe this would give you some hints

 

SELECT RNO, COUNT(*) FROM CUSTACC

GROUP BY RNO

HAVING COUNT(*) > 1

Share this post


Link to post

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);
 

Share this post


Link to post
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

Share this post


Link to post

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.

Share this post


Link to post

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"?

Share this post


Link to post
Guest
Posted (edited)

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 by Guest

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×