Jump to content
Sign in to follow this  
Henry Olive

Sql Update

Recommended Posts

Interbase

 

UPDATE  INVDETAIL ID
SET  ID.UPRICELOCAL=(ID.UPRICE-(ID.UPRICE * ID.DISCPERC /100)) * I.CURRRATE
WHERE EXISTS (SELECT 1 FROM INVOICE I where I.RNO = ID.RNO)


I'm getting   Column unknown I.CURRRATE  err.msg.

Thank You

 

 

Share this post


Link to post

My guess would be that I is in a sub-select, and not visible in the main expression?
I use MSSQL and not Interbase, though.

Share this post


Link to post

There is not alias I on assignment... So the error is correct. Replace I.CURRATE with select to get correct value..

Maybe?

SELECT I.CURRATE FROM INVOICE I WHERE I.RNO = ID.RNO

 

Share this post


Link to post
Guest

Exist many way to satisfact your demand. my tip is test the "SELECT" resulted individually, because "UPDATE SET" dont accept multi-records on resulted, ok! just on value is expected!

here some ways using just "SELECT" to see how many records is returned:

  • NOTE1:  when using ":XXXXXX", the query expect a "param", then, you needs inform it before "open it"
  • NOTE2: dont use "aliases" where can conflict with "field names", like:  ID.RNO ===> ID can be a field too! you see it?
  • NOTE2: "DISTINCT" avoid have records duplicated, but only if exist more than one with same value! not if the records have different values....!!!

 

image.thumb.png.20b9bfba2dbf69cbc19f1dd157010f7e.png

select a.ID, (a.UPRICE-(a.UPRICE * a.DISCPERC /100)) FinalPrice from INVDETAIL a
where exists(select 1  from Invoice b where b.RNO = a.ID) 

/* where (select distinct b.RNO from Invoice b where b.RNO = a.ID) not null */

/* where (select distinct b.RNO from Invoice b where b.RNO = :AID) not null

/* where exists(select distinct b.RNO from Invoice b where b.RNO = a.ID) */

/* where exists(select distinct b.RNO from Invoice b where b.RNO = :AID) */

/* , INVOICE b    where b.RNO = a.ID */

/* , INVOICE b    where b.RNO = :AID  */

/* where a.ID = 1 */

/* where a.ID = :AID */

 

hug

Edited by Guest

Share this post


Link to post
Guest

COMPARE THE RESULTED AND INDIVIDUAL VALUES FOR EACH INVOICE, MAINLY LAST 2 RESULTED (8+4=12), (8+16=24)

 

select a.ID, ((a.UPRICE-(a.UPRICE * a.DISCPERC /100))* b.CURRANTE) FinalPrice from INVDETAIL a, INVOICE b
 where exists(select 1  from Invoice b where b.RNO = a.ID)

image.thumb.png.db25a43a97f9cfc17dcdc7e6aebb8af4.png

 

select a.ID, ((a.UPRICE-(a.UPRICE * a.DISCPERC /100))* b.CURRANTE) FinalPrice from INVDETAIL a, INVOICE b
 where exists(select 1  from Invoice c where c.RNO = a.ID)

image.png.da826f839816504c54ffd77124909d72.png

 

select ((a.UPRICE-(a.UPRICE * a.DISCPERC /100))* b.CURRANTE) FinalPrice from INVDETAIL a, INVOICE b
where (b.RNO = a.ID)

image.png.c84542d57a45b0f1a9ddffa7903223e4.png

 

select distinct ((a.UPRICE-(a.UPRICE * a.DISCPERC /100))* b.CURRANTE) FinalPrice from INVDETAIL a, INVOICE b
where (b.RNO = a.ID)

image.png.f663c2f5171f82fa1b774ebb8ddbfead.png

 

select distinct b.ID, ((a.UPRICE-(a.UPRICE * a.DISCPERC /100))* b.CURRANTE) FinalPrice from INVDETAIL a, INVOICE b
where b.RNO = 2/*:bRNO*/ and (b.RNO = a.ID)

image.png.b0c15e0700b64f4d6b4042b1203c9098.png

 

select distinct b.ID, b.RNO, ((a.UPRICE-(a.UPRICE * a.DISCPERC /100))* b.CURRANTE) FinalPrice from INVDETAIL a, INVOICE b
where b.RNO = 1/*:bRNO*/ and (b.RNO = a.ID)

image.thumb.png.f1e44046e9a2e30716a8d6d49057f35f.png

 

select distinct a.ID aID, b.ID bID, b.RNO bRNO, ((a.UPRICE-(a.UPRICE * a.DISCPERC /100))* b.CURRANTE) FinalPrice from INVDETAIL a, INVOICE b
where b.RNO = 1/*:bRNO*/ and (b.RNO = a.ID)

image.thumb.png.fc69ca1e3ad69091d5d70f4814d325f4.png

 

select sum(((a.UPRICE-(a.UPRICE * a.DISCPERC /100))* b.CURRANTE)) FinalPrice from INVDETAIL a, INVOICE b
where  (a.ID = 1 /*:aID*/)

image.thumb.png.78c121658f27acd8f1ca1cac5d8afc2b.png

 

select sum(((a.UPRICE-(a.UPRICE * a.DISCPERC /100))* b.CURRANTE)) FinalPrice from INVDETAIL a, INVOICE b
where  (b.RNO = 1 /*:aID*/)

image.thumb.png.b90e7ec9af410d0ea29dfa72e2689da6.png

 

hug

Edited by Guest

Share this post


Link to post
17 hours ago, Henry Olive said:

Thank you so much for the time you spend on me EMailX45

I just need UPDATE a Detail Table field  according to a Master table's  field 

Can you please correct my below sql ?

UPDATE  INVDETAIL ID
SET  ID.UPRICELOCAL=(ID.UPRICE-(ID.UPRICE * ID.DISCPERC /100)) * I.CURRRATE
WHERE EXISTS (SELECT 1 FROM INVOICE I where I.RNO = ID.RNO)

 

Quote

 

 

 

 

Share this post


Link to post
20 hours ago, Henry Olive said:

Interbase

 

UPDATE  INVDETAIL ID
SET  ID.UPRICELOCAL=(ID.UPRICE-(ID.UPRICE * ID.DISCPERC /100)) * I.CURRRATE
WHERE EXISTS (SELECT 1 FROM INVOICE I where I.RNO = ID.RNO)


I'm getting   Column unknown I.CURRRATE  err.msg.

Thank You

 

 

Actually, the message is accurate.  You do not have a table alias visible in the set part named I. Assuming Currate is in the invoice table (what you aliased as I "I" the where clause tryt

 

UPDATE  INVDETAIL ID
SET  ID.UPRICELOCAL=(ID.UPRICE-(ID.UPRICE * ID.DISCPERC /100)) * (select I.CURRRATE from invoice i where i.rno = id.rno)
WHERE EXISTS (SELECT 1 FROM INVOICE I2 where I2.RNO = ID.RNO)

 

This part

 

(select I.CURRRATE from invoice i where i.rno = id.rno)

 

should reflect wherever currrate is.  

Share this post


Link to post
21 hours ago, Henry Olive said:

UPDATE  INVDETAIL ID
SET  ID.UPRICELOCAL=(ID.UPRICE-(ID.UPRICE * ID.DISCPERC /100)) * I.CURRRATE
WHERE EXISTS (SELECT 1 FROM INVOICE I where I.RNO = ID.RNO) 

Hi, try to replace your SQL by this one (I don't use Interbase but Firebird)

Quote

UPDATE  INVDETAIL ID
SET  ID.UPRICELOCAL=(ID.UPRICE-(ID.UPRICE * ID.DISCPERC /100)) *(SELECT CURRATE FROM INVOICE WHERE RNO=ID.RNO)

IMHO you will need a whatever rounding function for your result.

And, well :classic_huh:, I am not a fan of calculated columns in a table (except COMPUTED BY ones) 

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
Sign in to follow this  

×