update from select

[Former Member]

Hello,

i am trying to update a table using values from other table, i dont find how to do it in maxdb, i am trying with this:

UPDATE ALMACEN.UBICACION_STOCK SET STOCK=STOCK-LIN.UNIDADES, RESERVADO=RESERVADO+LIN.UNIDADES FROM OTEROS.ALBARAN_LINEA AS LIN JOIN ALMACEN.UBICACION_STOCK AS UB ON LIN.ID_ARTICULO=UB.ID_ARTICULO WHERE SUBSTR(UB.ID_UBICACION, 1, 3) = 200 AND LIN.ID_ALBARAN=2000000100

but i get an error in the FROM keyword, any other way to accomplish this?

Thanks.

[Former Member · Accepted solution]

Hi,

as update does not allow joins in the set-clause, I am not astonished about your error.

Try this:

UPDATE ALMACEN.UBICACION_STOCK UB

SET

STOCK=STOCK -

(select LIN.UNIDADES

from OTEROS.ALBARAN_LINEA AS LIN

where LIN.ID_ARTICULO=UB.ID_ARTICULO

and SUBSTR(UB.ID_UBICACION, 1, 3) = 200

and LIN.ID_ALBARAN=2000000100) ,

RESERVADO=RESERVADO+

(select LIN.UNIDADES

from OTEROS.ALBARAN_LINEA AS LIN

where LIN.ID_ARTICULO=UB.ID_ARTICULO

and SUBSTR(UB.ID_UBICACION, 1, 3) = 200

and LIN.ID_ALBARAN=2000000100)

May be, this will do, although calculating the same thing twice does not seem the best way of doing it.

Elke

[Former Member · Accepted solution]

I have tried and i get this error:

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

Integrity constraint violation;-8006 POS(1) Data types must be compatible

but stock, reservado and unidades are all integer. this works for example.

SELECT UB.STOCK-LIN.UNIDADES, UB.RESERVADO+LIN.UNIDADES FROM ALMACEN.UBICACION_STOCK AS UB JOIN OTEROS.ALBARAN_LINEA AS LIN ON UB.ID_ARTICULO=LIN.ID_ARTICULO WHERE LIN.ID_ALBARAN=2000000100

other question, if i have more than one row for the query will be all updated?

[Former Member · Accepted solution]

Hi,

as I tried a similar update and succeeded with the newest version, I would like to know your exact kernel version and the table definition for the tables concerned.

Yes, all rows passing the qualification will be updated with one sql statement.

Elke

[Former Member · Accepted solution]

maxdb version it is 7.6.03.15 in a Linux machine.

tables are:

CREATE TABLE ALMACEN.UBICACION_STOCK (

ID_UBICACION INTEGER NOT NULL,

ID_ARTICULO INTEGER NOT NULL,

STOCK INTEGER NOT NULL,

RESERVADO INTEGER DEFAULT 0,

PRIMARY KEY (ID_UBICACION, ID_ARTICULO)

)

//

CREATE TABLE OTEROS.ALBARAN_LINEA (

LINEA_ID INTEGER NOT NULL,

ID_ARTICULO INTEGER NOT NULL,

PRECIO_COSTO FIXED(7,2) NOT NULL,

IVA INTEGER NOT NULL,

UNIDADES INTEGER NOT NULL,

ID_ALBARAN INTEGER NOT NULL,

DTO_LINEA FIXED(5,2) DEFAULT 0.00,

ID_TARIFA INTEGER NOT NULL,

PVP_TEORICO FIXED(7,2) NOT NULL,

PVP_REAL FIXED(7,2) NOT NULL,

TOTAL_LINEA FIXED(7,2) NOT NULL,

PRIMARY KEY (LINEA_ID, ID_ALBARAN)

)

//

Thanks.

[Former Member · Accepted solution]

Hi,

here we are. The problem is:

SUBSTR(UB.ID_UBICACION, 1, 3) = 200

with

ID_UBICACION INTEGER NOT NULL,

a SUBSTR of an integer is a little bit .... let's say: uncommon.

(And as it is not in your select-example, this example works.)

SUBSTR (CHR ( UB.ID_UBICACION ), 1, 3 ) = 200

may be correct.

But are you really sure, that 200, 2001, 20012, 200123 ...

all should succeed?

Are you really sure, that there are no additional blanks in the beginning, if your integer is smaller than the maximum number of digits allowed?

Or do the integer all have the same number of digits and you want to find those with the same prefix? Then this method or a check using function DIV may help. Otherwise, perhaps some SUBSTR (LTRIM (CHR (...) ) , 1, 3) may help.

Elke

[Former Member · Accepted solution]

I know can seem uncommon but i have been doing it in maxdb many times, i think it is converted to string in background, this works too:

SELECT UB.STOCK-LIN.UNIDADES, UB.RESERVADO+LIN.UNIDADES FROM ALMACEN.UBICACION_STOCK AS UB JOIN OTEROS.ALBARAN_LINEA AS LIN ON UB.ID_ARTICULO=LIN.ID_ARTICULO WHERE LIN.ID_ALBARAN=2000000100 AND SUBSTR(UB.ID_UBICACION, 1, 3) = 200

i have tried this:

UPDATE ALMACEN.UBICACION_STOCK UB SET STOCK=STOCK-(select LIN.UNIDADES from OTEROS.ALBARAN_LINEA AS LIN where LIN.ID_ARTICULO=UB.ID_ARTICULO and SUBSTR(CHR(UB.ID_UBICACION), 1, 3) = 200 and LIN.ID_ALBARAN=2000000100), RESERVADO=RESERVADO+(select LIN.UNIDADES from OTEROS.ALBARAN_LINEA AS LIN where LIN.ID_ARTICULO=UB.ID_ARTICULO and SUBSTR(CHR(UB.ID_UBICACION), 1, 3) = 200 and LIN.ID_ALBARAN=2000000100)

and now i get this:

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

General error;300 POS(1) Integrity violation:STOCK,UBICACION_STOCK,ALMACEN

the meaning of this check it is that every warehouse has a code, 200 in this example, and every warehouse has different locations for stock, every location has his own code but all begins with the same code of the warehouse (200 in this example), so if i want to update all the stock the invoice id 2000000100 has modified in the warehouse i need to identify all locations that begins with 200 or add a column warehouse to almacen.ubicacion_stock and filter with it.

anyway i have tested this, because i only have a location now i can do it:

UPDATE ALMACEN.UBICACION_STOCK UB SET STOCK=STOCK-(select LIN.UNIDADES from OTEROS.ALBARAN_LINEA AS LIN where LIN.ID_ARTICULO=UB.ID_ARTICULO and LIN.ID_ALBARAN=2000000100), RESERVADO=RESERVADO+(select LIN.UNIDADES from OTEROS.ALBARAN_LINEA AS LIN where LIN.ID_ARTICULO=UB.ID_ARTICULO and LIN.ID_ALBARAN=2000000100)

and fails the same way:

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

General error;300 POS(1) Integrity violation:STOCK,UBICACION_STOCK,ALMACEN

[Former Member · Accepted solution]

maybe this is a kernel problem? any other way to get that i need from a sql statement with maxdb? Thanks.

[Former Member]

Hi,

if I try to reproduce that problem, I am only able to reproduce in case the (correlated) subquery does not return a result, meaning the update is done with a NULL-value, causing the NOT NULL-integrity constraint to fail --> 300 integrity violation

If at some point in time it is possible that this update is done for values, where the subquery will not have one resultrow, your problem may occur.

I do not know how your insert-sequence is done for new objects. If it is done with one transaction or two. I do not know the isolation level this update (and implicit selecting) is done or if it may happen that non-committed rows of parallel inserts may be seen (and those parallel transactions did not succeed in including the corresponding row in the other table so far).

Could you please check this idea?

If you do not succeed in finding the reason, please follow the wiki

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/maxdb/maxdbSupportGuide&

search for vtrace, use the first document found,

do not only do trace_on default, but trace on default,

trace_on select and trace_on update

(same with trace_off)

Then do you statement, trace_off, prepare vtrace and provide it.

Best regards,

Elke

[Former Member]

Hi,

It COULD be a problem of the kernel.

But it could be a problem of the stored data.

Error 300 tells us, that the resulting value does not fit into the explicitly oder implicitly defined value-range for a value in column stock.

Stock is defined as integer, meaning fixed (10) in the range of

-2147483648 to 2147483647. Usually that should fit for stocks

(even for nails or such kind of stuff).

Perhaps some

select stock, stock - (select LIN.UNIDADES from OTEROS.ALBARAN_LINEA AS LIN where LIN.ID_ARTICULO=UB.ID_ARTICULO and LIN.ID_ALBARAN=2000000100) from ALMACEN.UBICACION_STOCK UB

may show us the value(s) causing the trouble.

Otherwise we will have to check internals with vtrace and so on.

Elke

[Former Member]

the range values generated from this operation are between 20 and -20, so it should be valid to integer, how i can activate vtrace and send you the trace? Thanks.

[Former Member]

i am having the same problem now without join.

UPDATE ALMACEN.UBICACION_STOCK ST SET STOCK=(SELECT STOCK_REAL FROM ALMACEN.REGULARIZACION RE WHERE RE.ID_INVENTARIO=2000000264 AND RE.ID_ARTICULO=ST.ID_ARTICULO)

all stock, stock_real, id_inventario, re.id_articulo, st.id_articulo are integers here but i get:

Error -

---

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

General error;300 POS(1) Integrity violation:STOCK,UBICACION_STOCK,ALMACEN

UPDATE ALMACEN.UBICACION_STOCK ST SET STOCK=(SELECT STOCK_REAL FROM ALMACEN.REGULARIZACION RE WHERE RE.ID_INVENTARIO=2000000264 AND RE.ID_ARTICULO=ST.ID_ARTICULO)

how can i send you the vtrace?