Statement SQL: UPDATE

[Former Member]

Hi all,

can you insert into one Command a SELECT and in another Command an UPDATE?

Thanks a lot

[ido_millet]

A single command can include an Update statement followed by a Select statement.

[Former Member]

Ido is 100% correct. You can have as many SQL statements as you would like in a single command just as long as it only passes a single data set back to CR. If you are going to use a Command as your data source, you should make every effort the get everything into a single Command.

Jason

[Former Member]

How to divide the update and the select into the Command? Make me an example, please.

[Former Member]

As an example...

UPDATE TableName
SET FieldName = "FieldValue"
WHERE FieldName = SelectionCriteria;

SELECT * FROM TableName;

Some database require a ; to delimit separate statements other do not... It's considered "proper syntax", according to the ANSI standard, so nearly all (if not all) databases will accept it.

HTH,

Jason

[Former Member]

1)if I write update and select statements into a Command, Crystal Reports 2008

gives me an error, ORA-00911: invalid character (I use Oracle as DBMS).

The same error gives me Toad if I run the update and select statement

as "execute statement"

2)update and select statement are executed by Toad rightly if I run as

"execute as a script". How can I specify this in Crystal Reports 2008?

3)after the update I have to insert the commit otherwise the table is locked

Thank you very much

[Former Member]

Unfortunately, that sounds like an Oracle specific issue... and I'm a SQL Server guy, so I don't know the specific Oracle syntax.

I would have to imagine that (but I don't know this for sure) there would be some sort of command to specify that directly in the SQL.

CR will pass any SQL that's in a command back to the database exactly as it is written. Another option is to write a stored procedure...

If all else fails... go back to your original idea of using 2 separate commands. It will work but CR will complain about it.

HTH,

Jason

[Former Member]

It's been a really long time since I've used Oracle, so this is a WAG...

Can you wrap the two statements with BEGIN / END?

HTH,

Carl

[Former Member]

Hi Carl,

I wrapped the two SQL statements with BEGIN / END, within Crystal Reports 2008's Command but gives me an error. How can I make? Specially:

1)writing two separate Command (one for the Select and one for the Update), why the update does not always work fine

2)how do I put 'commit' after the Update statement, to avoid blocking the table

Thank you.

Edited by: Francesco83 on Apr 28, 2010 11:51 AM

[Former Member]

At this point you are dealing with Oracle syntax issues... I've never reported from an Oracle DB and it looks like Carl is a little rusty in that area as well.

Just keep 1 thin in mind... CR will send any SQL statement, exactly as it is written, to the database. That means that if you can execute it from TOAD or the Oracle SQL Developer, you should be able to do it from CR as well.

If that's not the case, check and make sure that you are using the correct database driver (or at least the same driver that TOAD is using).

I don't know if this applies to Oracle... but with SQL Server, anything you can do using the SSMC's user interface, can also be done by writing the appropriate SQL. The UI is simply there to same a little time... I have to imaging Oracle is the same in that regard. A little Googleing should find you the proper syntax.

HTH,

Jason

[Former Member]

Hi, I noticed this threat was still an open item; has there been a resolution as of yet? I have a similar situation...

I need to add a value to a column if that value does not exist. I am fairly new to sql and was trying to use the Update command with the select statement (ex:

SELECT "VW"."HE"

FROM "AM_FACADE"."NM_OUTBOUND_SCHEDULE_BID_V" "VW"

UPDATE "VW"

SET "VW"."HE" = IN['1', '2', '3', '4', '5', '6', '7', '8', '9', '10' ,'11', '12','13','14','15','16','17','18','19','20','21','22','23','25','25']

WHERE "VW"."HE" = IN['1', '2', '3', '4', '5', '6', '7', '8', '9', '10' ,'11', '12','13','14','15','16','17','18','19','20','21','22','23',"24"]

Basically I want it to check the HE column to see if the 25th hour exist, if not add it.

Any help would be appreciated.

[Former Member]

TIPRICH25,

Word of caution... You should NEVER EVER EEEVVVEEERRR use UPDATE, INSERT or DELETE statements in your SQL Commands until you are VERY comfortable hand coding SQL and you are 100% confident in it's results.

Databases don't have "undo" buttons and if you overwrite or delete data in your tables by mistake, there is no getting it back w/o restoring from a back up.

Besides... If all you want to do is append dummy data to a result set, a UNION SELECT will probably work better for you.

HTH

Jason

[Former Member]

Thank you for the caution Jason,

Actually I created a view and stored it in a different (development) schema, I was trying to write the script off the view already created in toad.

I need to actually add a row to a column if that row does not exist. As I look further into this, I believe the insert option would better do the job.

[Former Member]

A view is simply a "virtual" table. There's nothing (other than the underlying tables) to insert anything into.

Odds are you'd be better off creating a temp table that has values ranging 1 - 25 and then using a FULL OUTER JOIN to join it to your view.

Jason

[Former Member]

That did it, so simple.

Thank you so much.