Update table using a secind table

[jmckee]

I need to update a field in table, using data from another table. The second table will have the old field value and the new field value.

Can this be don with a single UPDATE command, or is a procedure required?

I have written a procedure, but get an error on a DECLARE STATEMENT (Invalid syntax).

The error is on the DECLARE CURSOR statement, below.

I am new to SQL. I would appreciate any assistance.


begin
declare newinvfile cursor for
select "old inventory number", "new inventory number", "description"
from "new inventory"
order by "old inventory number";
declare oldinv char(12);
declare newinv char(12);
declare desc char(50);
open newinvfile;
fetch first from newinvfile into OLDINV, NEWINV, DESC;
while (SQLSTATE <> '02000')
update &quot;base inventory&quot; b
set b.&quot;inventory number&quot; = newinv
where b.&quot;inventory number&quot; = oldinv;
fetch next from newinvfile into oldinv, newinv, desc;
end while;
end

 

[Vancouverite]

You could use this query instead: (This assumes SP3+ of P.SQL)

UPDATE &quot;Base Inventory&quot; As b SET b.&quot;Inventory Number&quot; =
(SELECT N.&quot;New Inventory Number&quot; FROM &quot;New Inventory&quot; As N WHERE N.&quot;Old Inventory Number&quot; = b.&quot;Inventory Number&quot

What error are you getting? I can't see a specific syntax error above, unless it's the missing variable declaration character &quot;:&quot; (which is needed in PSQL). Try the below SP:

begin
declare newinvfile cursor for
select &quot;old inventory number&quot;, &quot;new inventory number&quot;, &quot;description&quot;
from &quot;new inventory&quot;
order by &quot;old inventory number&quot;;
declare ldinv char(12);
declare :newinv char(12);
declare :desc char(50);
open newinvfile;
fetch first from newinvfile into :OLDINV, :NEWINV, ESC;
while (SQLSTATE <> '02000')
update &quot;base inventory&quot; b
set b.&quot;inventory number&quot; = :newinv
where b.&quot;inventory number&quot; = ldinv;
fetch next from newinvfile into ldinv, :newinv, :desc;
end while;
end

 

[jmckee]

All I get is a message stating that the SQL statement contains invalid syntax. The first portion of my procedure is shown, without the terminating semicolon (which IS in the procedure). What is shown for the statements performed is:

begin
declare newinvfile cursor for
select &quot;old inventory number&quot;, &quot;new inventory number&quot;, &quot;description&quot;
from &quot;new inventory&quot;
order by &quot;old inventory number&quot;

And the cursor is positioned immediately to the right of the quotation mark at the end of the order by clause.

I am confused by the use of the ':' in your example. But, then, I only have the reference manuals, and I have already seen several weaknesses in them.

 

[jmckee]

I just tried the update statement. Here is what I entered:

update &quot;base inventory&quot; as b set b.&quot;inventory number&quot; =
(select n.&quot;new inventory number&quot; from &quot;new inventory&quot; as n
where n.&quot;old inventory number&quot; = b.&quot;inventory number&quot


When I tried the statement, I received an error:

Scalable SQL engine error = 507. The UPDATE statement is invalid.

Any thoughts?

 

[Pervasivite]

Yes, you are using Pervasive.SQL 7 which requires Scalable SQL Syntax. The current product Pervasive.SQL 2000i uses native ODBC syntax which is a bit different. You can get the docs for Scalable SQL from the Pervasive Web site,

http://www.pervasive.com,

and find out how to convert the statement to the proper SSQL syntax.

Regards,
Pervasivite