Irricad

Hi.
I'm using IrriBase 20.07 (The latest update).
I'm trying to write a SQL query to update pipes usage that will do this:
1. clear all pipe usages (the first UPDATE Statment in the code below).
2. assgin new usages to pipes (the second and third UPDATE Statments in the code below)

in the test query I worte (see below) thre are 3 update statments, and if I run each one of them individually they works,
but when trying to run them togther I get An error "Characters found after end of SQL statement".
I've looked in SQL forums and it looks like the syntax is ok.

here is the query I'm trying to run (3 UPDATE statments in one query):

UPDATE Components
SET Components.[USAGE ]= ' ' WHERE Components.[Database Number]=1 AND (Components.[USAGE] <> ' ' and Components.[USAGE] <> 'L ');

UPDATE Components
SET Components.[USAGE] = 'M ' WHERE Components.[Database Number]=1 AND (Components.[Description] = 'PE IRRIGATION PIPE 90/6');

UPDATE Components
SET Components.[USAGE] = 'M ' WHERE Components.[Database Number]=1 AND (Components.[Description] = 'PE IRRIGATION PIPE 110/6');

Any help will be appreciated.
Thanks,
Ari.

Hi Ari,

You can used the tilde symbol (~) to concatenate separate SQL statements that will then run consecutively. e.g.

UPDATE Components SET Components.[USAGE]= ' ' WHERE Components.[Database Number]=1 AND (Components.[USAGE] <> ' '
AND Components.[USAGE] <> 'L ')~UPDATE Components SET Components.[USAGE] = 'M ' WHERE Components.[Database Number]=1
AND (Components.[Description] = 'PE IRRIGATION PIPE 90/6')~UPDATE Components SET Components.[USAGE] = 'M ' WHERE
Components.[Database Number]=1 AND (Components.[Description] = 'PE IRRIGATION PIPE 110/6')

WOW that was fast...
Thanks Dan - works like a charm!
the tilde symbol (~) was indeed the missing link...
I've changed the query a little using "OR" condition when adding the usage letter so I won't need to click YES for every pipe "UPDATE" statment, only 2 clicks - one for the clearing statment and the second to the pipes change, see below (and thanks again!!):

UPDATE Components SET USAGE = ' ' WHERE [Database Number]=1 AND (USAGE <> ' ' and USAGE <> 'L ')~
UPDATE Components SET USAGE = 'M ' WHERE ([Database Number]=1 AND
Description = 'PE IRRIGATION PIPE 90/6')
OR (Description = 'PE IRRIGATION PIPE 110/6')
OR (Description = 'PE IRRIGATION PIPE 20/6');

*comment for those who want to use it:
you can add as many "OR" lines - just make sure to add at the end of the LAST line (LAST LINE ONLY!!) this symbol: ";"
in SQL language It means "end of statment".

Hi Ari - another thing you can do is add a SELECT statement at the end to display the results of your updates, just to check everything worked as expected. You might find the SQL "LIKE" operator useful too.

Hi Dan and thanks for the ideas!!
After remebering this thread viewtopic.php?p=2044&hilit=sql#p2044 and watching last year webinar lesson No.10 I've went for the excel approach to the export and update tasks.

I'm using 2 queries based on above lessons:
1. Export Items (Except Nozzles) To Excel:
INSERT INTO [Excel 8.0;HDR=YES;IMEX=0;Database=C:\Temp\test5.xls].[Sheet1$]
SELECT Description, [Warehouse Code] AS WarehouseCode, [Supplier Code] AS SupplierCode, USAGE, [Database Number] AS DatabaseNumber FROM Components
WHERE ([Database Number] <14)

2. Change Components Usage:
DROP TABLE Temp~UPDATE Components SET Components.Usage=' 'WHERE [Database Number]<12 AND Flags<>2~SELECT T1.* INTO Temp FROM [Excel 8.0;HDR=YES;IMEX=0;Database=C:\Temp\test6.xls].[Sheet1$A1:AZ65536] AS
T1~UPDATE Components INNER JOIN Temp ON Components.[Warehouse Code]=(Temp.[Warehouse Code]) SET Components.Usage=Temp.Usage~DROP TABLE Temp~SELECT Components.Description, Components.[Warehouse Code],
Components.Usage FROM Components WHERE [Database Number]<14 AND Flags<>2

the use of the Database Number column enable me to filter the items in the excel files by groups so I can use one file for each task instead of many files (one for each item group) for the Components Usage change task.
I then keep a separate list of the usage for differant scenarios (PVC piping,PE piping etc') and update the Components Usage file (test6.xls)before running the update query.

the reason for the 2 files (one for the export to excel and one for the usage change task) is that in the export to excel task the SQL query dosn't allow to use 2-words column header (for example - Warehouse Code must be WarehouseCode in the Excel file) and for the Components Usage change query I must have the column headers the same as in the database.

I've attached the 2 excel templates.
If you have additional comments or ideas for improvement, I would appreciate it if you shared.

thanks,
Ari.

Hi Ari,

For export, it doesn't actually matter what the columns in the Excel Sheet are called, as long as there are the correct number and they are different to the names used in the query (either directly or as aliases). This means you can use a query like this:

INSERT INTO [Excel 8.0;HDR=YES;IMEX=0;Database=C:\Temp\Test.xls].[Sheet1$] SELECT Description, [Warehouse Code] AS WS,
[Supplier Code] AS SC, Usage, [Database Number] AS DN FROM Components WHERE ([Database Number] < 14)

but have the names in the sheet be 'correct' (i.e., Description, Warehouse Code, Supplier Code, Usage, Database Number).
That would save you a step, because you could use the same file.

Alternatively, you can avoid the aliases altogether by specifying the table name explicitly in the query

INSERT INTO [Excel 8.0;HDR=YES;IMEX=0;Database=C:\Temp\Test.xls].[Sheet1$] SELECT Components.Description,
Components.[Warehouse Code], Components.[Supplier Code], Components.Usage, Components.[Database Number]
FROM Components WHERE (Components.[Database Number] < 14)

Regards,

Dan

Hi Dan and thanks for the reply!
using the explicit table name as As you suggested did solved the issue,
And this is one Excel worksheet table that now both gets the data from the database and updates the item usage.
thanks again,
Ari.