Sql Update

[Henry Olive 5]

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

 

 

[Lars Fosdal 1792]

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.

[Virgo 18]

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

 

[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....!!!

 

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 January 19, 2021 by Guest

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

 

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)

 

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

 

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

 

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)

 

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)

 

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)

 

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

 

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

 

hug

Edited January 19, 2021 by Guest

[Henry Olive 5]

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

 

 

 

 

[Jeff Overcash 2]

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.  

[Serge_G 87]

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 , I am not a fan of calculated columns in a table (except COMPUTED BY ones) 

[Henry Olive 5]

Thank you SO MUCH  Jeff  it is Perfect

Thank you Serge