Solved

SQL: ALTER and UPDATE with one statement?

Posted on 2014-04-22
46
429 Views
Last Modified: 2014-04-22
I'm trying to insert two new columns into a table. But I'm also wanting to update the rows right afterwards, using a single statement.

So for example I want to add a SALARY and DOB column into the table. But I want to only update employee number 1 and number 3 with the new information information I have for in the rows.


Is it possible to do this in one statement? I don't believe it can be done without a stored procedure from what I've read up on.

Can someone help me out on this?

(Using oracle)
0
Comment
Question by:Pancake_Effect
  • 21
  • 18
  • 6
46 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40014948
Altering a table to add columns is called DDL, Data Definition Language.

Modifying data in tables is called DML, Data Manipulation Language.

You cannot do DDL and DML in a single statement.


Why would you want to?
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40014969
The goal is to save time by basically doing it in one big batch essentially. Hmm I guess that makes sense, I didn't realize you can't use DDL and DML statements at the same time within a stored procedure or trigger.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40014979
>>The goal is to save time by basically doing it in one big batch essentially.

Typically I do things like this in a SQL script.  Connect using sqlplus and execute one large script.

>> you can't use DDL and DML statements at the same time within a stored procedure or trigger.

I didn't say that.  I said a single statement.

You can do whatever you want in code.

That said:  Whenever I hear about DDL inside a procedure or trigger, I think bad design.

You should never have to do this.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015001
For example, create a file called c:\myscript.sql with the following contents:

create table tab1 (col1 char(1));
insert into tab1 values('a');
commit;

alter table tab1 add col2 char(1);

alter table tab1 rename column col1 to colx;

update tab1 set col2='z';
commit;

Open in new window



Then from a command line:
sqlplus username/password @C:\myscript.sql

Note:It's a bad idea to put the username and password on the command line in Unix since other suers can see it.

If you are in unix, just do:
sqlplus /tmp/myscript.sql

You will be prompted for the username and password
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015005
Ah okay. Well here is an example of what I understand how to do seperatly. Basically a person can spend the time to make the necessary changes, then hand it off to the person to simply do a quick run on it.

ALTER TABLE CUSTOMER
ADD  CUST_DOB, CUST_AGE

UPDATE CUSTOMER
SET CUST_DOB='May 15, 1969'
WHERE CUST_NUM=13

UPDATE CUSTOMER
SET CUST_DOB='Janurary 19, 1948'
WHERE CUST_NUM=16


Is it possible to combine this into one statement or within code?
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015008
Oops just saw your post above, I'll take a look
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015017
>> I'll take a look

Forgot one thing.  When creating scripts, you should always spool out the output to check for errors.

first line of the script:
spool myscript.txt

last line:
spool off

Then you have a myscript.txt file to review to make sure everything is 'good'.

If you leave off the '.txt' the default is a .lst extension.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015038
create table tab1 (col1 char(1));
insert into tab1 values('a');
commit;

alter table tab1 add col2 char(1);

alter table tab1 rename column col1 to colx;

update tab1 set col2='z';
commit;

On your script your creating a new table. I know it's just for example, but in my situation would this look okay? (I'm entering this from the oracle command gui window.

commit;
ALTER TABLE CUSTOMER
ADD  CUST_DOB, CUST_AGE

UPDATE CUSTOMER
SET CUST_DOB='May 15, 1969'
WHERE CUST_NUM=13

UPDATE CUSTOMER
SET CUST_DOB='Janurary 19, 1948'
WHERE CUST_NUM=16
commit;


Sorry I'm a huge noob at this! I know that has to be wrong.

I've also seen some scripts in a book I bought that state using a BEGIN and END so like:

BEGIN
ALTER TABLE CUSTOMER
ADD  CUST_DOB, CUST_AGE

UPDATE CUSTOMER
SET CUST_DOB='May 15, 1969'
WHERE CUST_NUM=13

UPDATE CUSTOMER
SET CUST_DOB='Janurary 19, 1948'
WHERE CUST_NUM=16
END


How would something like that work?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 384 total points
ID: 40015059
>>I'm entering this from the oracle command gui window

What GUI are you using?

>>. I know it's just for example, but in my situation would this look okay?

You need ';' after your alter and update statements and column data types on the alter:
ALTER TABLE CUSTOMER
ADD  CUST_DOB DATE, CUST_AGE NUMBER;

UPDATE CUSTOMER
SET CUST_DOB='May 15, 1969'
WHERE CUST_NUM=13;

UPDATE CUSTOMER
SET CUST_DOB='Janurary 19, 1948'
WHERE CUST_NUM=16;
commit;

Open in new window



>>I've also seen some scripts in a book I bought that state using a BEGIN and END so like:

BEGIN and END are for PL/SQL blocks.  Either stand-alone or procedural code.

You cannot perform DDL inside a pl/sql block.  That is when you get into the execute immediate area.  It's a bad idea.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015072
oops.  missed something:

SET CUST_DOB='Janurary 19, 1948'

Unless your NLS_DATE_FORMAT is set to that mask, that will fail.

Get into the habit of explicit data conversions:

SET CUST_DOB=to_date('Janurary 19, 1948','Month DD, YYYY')

You can use any format mask you want.  The online docs have all of them available.

For example, this will also work:
SET CUST_DOB=to_date('1/19/1948','MM/DD/YYYY')
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015092
GUI
Which way will make it work in this GUI?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015101
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015105
>>Which way will make it work in this GUI?

Sorry but there is nothing in that screen shot that tells me what GUI/Tool you are using.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40015106
Not specifically related to your question, but I wanted to add something here that you should be aware of.

Since you're doing DDL / DML together - be aware that any time you do DDL, there's an implicit commit performed.

So if you have this:

update tab1 set col1=1 where col2 = 2;

alter table tab1 add col3 number;

rollback;


You should be aware that the alter table will implicitly commit that update and you can't roll it back.

As I said, not specifically related to this question, but since you state "Sorry I'm a huge noob at this! "  wanted to make sure you're aware of it so you don't have any other surprises down the road.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015113
Excellent point Steve.  I thought of if but forgot to mention it.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015119
>Which way will make it work in this GUI?

>>Sorry but there is nothing in that screen shot that tells me what GUI/Tool you are using.

When you open Oracle it's the enter command button on the main screen if that helps at all?

gui
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015128
>>When you open Oracle it's the enter command button on the main screen if that helps at all?

You don't open 'Oracle'.  You run some program that allows you to access Oracle.

You are showing me a tire and having me guess what car it is.

What is the program name (typically found with a Help/About)?
What is the label of the icon you are clicking on?
Something...

That said:  Look at the SQL Scripts menu item.  Not knowing the program, that would be where I would start with SQL scripts.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015196
It's just the free version I'm practicing on right now, "Oracle Database Express Edition"
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015212
I'm not sure what GUI Oracle XE comes with so I cannot say with any accuracy how to execute a script from it.

I would start off with the "SQL Scripts" menu item.

I've always used the sqlplus command line.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015218
Ah okay I see the sqlplus command line there on the menu, I will give that a whirl quick
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015246
I'm getting a "not connected" error on that. Also took out the spaces and made it one line, same thing. Any thoughts?

connect
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015254
After running sqlplus did it not prompt you for a username and password?

Anyway, you need to connect to the database.  From the SQL prompt:
conn username/password

You should see a "Connected." type response.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40015272
Possibly you may need to provide the connect string.

(On my machine I have a generic test database setup called demo, with a login of demo).

If you run sqlplus from the start menu, it may prompt you for a login and since you appear to be on Windows, you probably need to provide the service name you're connecting to (which means you need your tnsnames.ora setup):

SQLPLUS - From start menu
Alternatively, you could do Start / Run and open a CMD window, set ORACLE_SID and connect that way, without a connect string:

SQLPLUS - From CMD Window
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015285
Ah okay, connected now, but still error..not letting me create the table for some reason it looks like:

error
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015290
>> you probably need to provide the service name you're connecting to (which means you need your tnsnames.ora setup):

The OP stated they are running XE.  You cannot change the SID, it is XE by default.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015293
>>but still error..not letting me create the table for some reason it looks like:

Take note of what I posted above.  On the ALTER TABLE, you need the data types.

Look at my alter table command in http:#a40015059.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015315
Oops missed that, I added the identifier in. Still getting same error though :(

Error
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015328
>>I added the identifier in. Still getting same error though :(

Should have tested before I posted.  You need parans for multiple columns:

ALTER TABLE CUSTOMER ADD  (CUST_DOB DATE, CUST_AGE NUMBER);
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015344
Hmm same thing here's the exact code I'm pasting in, looks like it's still getting hung up on the ALTER command

ALTER TABLE CUSTOMER
ALTER TABLE CUSTOMER ADD  (CUST_DOB DATE, CUST_AGE NUMBER);

UPDATE CUSTOMER
SET CUST_DOB=to_date('March 15, 1979','Month DD, YYYY')
WHERE CUST_NUM=1000;

UPDATE CUSTOMER
SET CUST_DOB=to_date('December 22, 1988','Month DD, YYYY')
WHERE CUST_NUM=1001;
commit;

Open in new window


error
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 116 total points
ID: 40015353
In this example you've done:

alter table customer alter table customer add ....

Just one alter table customer will do :)
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015364
haha that was it xD

edit: Just got to double check quick that it reflected correctly
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015389
Everything looks good, but for some reason it didn't take the date format of Month DD, YYYY. Instead it's using:

DD-Mon-YY

Would there be a reason why it would do that? Def a minor issue, but just curious.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015400
>>Month DD, YYYY. Instead it's using: DD-Mon-YY

See my comment above:  http:#a40015072

The default date mask is DD-Mon-YY.  To use anything else you need to set NLS_DATE_FORMAT or explicitly set the format mask.

I strongly encourage you to get into the habit of setting the mask and not mess with 'default' formats.
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 116 total points
ID: 40015406
You get that when you query it ?

You specified the date mask on the insert - so it matched that format.

The default date mask is DD-Mon-YY on select of a date data type.

Either of these would work:

alter session set nls_date_format('Month DD, YYYY');
select blah, blah

Or:

select to_char (cust_dob, 'Month DD, YYYY');


The former is probably preferred since then you're not doing data type conversion, just altering the way date is displaying
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015466
I see those commands will change it when I query it in that fashion temporarily. However after doing a simple SELECT * FROM CUSTOMER...it still shows in the DD-Mon-YY format. Which is the expected I imagine.

Is there something I should have originally done with the column to make the data display in Month DD, YYYY when I first set it up? Or is that simply the default if you do a  SELECT * FROM TABLE?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015481
>>Or is that simply the default if you do a  SELECT * FROM TABLE?

My note above, again: NLS_DATE_FORMAT.

You can alter the session as shown above or set the default for the ENTIRE DATABASE with an 'alter system' (I DO NOT SUGGEST DOING THAT).

I still suggest you get in the habit of explicit conversions:
select to_char(CUST_DOB,'Month DD, YYYY') from CUSTOMER;

Then you do not mess with the output from any other queries that would return a date.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015504
Maybe it is time for this explanation common to people new to Oracle:

Oracle dates are stored in an internal format.  They do not have a human readable format so when inserting and selecting, if you do not explicitly convert the string to a date or the date to a string, Oracle does an implicit conversion for you.

The default string format is 'DD-Mon-YY'.  You can override the default with NLS_DATE_FORMAT (for dates, timestamps is NLS_TIMESTAMP_FORMAT).

NLS_DATE_FORMAT can be set for just your session: alter session
or the entire database: alter system

The best way to guarantee you KNOW what you need is an explicit conversion with TO_DATE to convert a string to date and TO_CHAR to convert a date to a string.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015532
Thanks for clearing that up, that makes a lot more sense now.

However on the:

>>The best way to guarantee you KNOW what you need is an explicit conversion with TO_DATE to convert a string to date and TO_CHAR to convert a date to a string

As you can see above, I did indeed enter that, "SET CUST_DOB=to_date('Janurary 19, 1948','Month DD, YYYY')" But I guess I'm having hard time understand what that actually accomplishes? You state that it does a explicit conversation, but where is that converted data in that format to view?
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 116 total points
ID: 40015546
All the to_date does on the insert / update is to convert a human readable date into Oracle's internal format.

As slightwv said - on the viewing of said data you need to convert back to human readable format in one of two ways:

* use to_char to convert back from Oracle internal format to human readable column by column
* use alter session and set NLS_DATE_FORMAT to do it automatically when it sees a date

The reason you see DD-Mon-YY is that this is the DEFAULT setting for NLS_DATE_FORMAT.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015547
>>You state that it does a explicit conversation, but where is that converted data in that format to view?

It took a string and converted it to an Oracle date.  You really cannot 'view' the internal date.  Whenever you select it you view it as a string.  If you do not specify what string format when selecting it with TO_CHAR, Oracle uses the default or whatever NLS_DATE_FORMAT is set to.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015549
>>The reason you see DD-Mon-YY is that this is the DEFAULT setting for NLS_DATE_FORMAT.

Small correction:  That is the DATABASE default.  NLS_DATE_FORMAT has no default setting.  You can check V$PARAMETER and NLS_DATE_FORMAT can be null.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40015555
Ah okay, so should of I put this instead of to_date?

SET CUST_DOB=to_char('March 15, 1979','Month DD, YYYY')
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 116 total points
ID: 40015560
No.  

Use to_date when setting the date value.
Use to_char when fetching.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 384 total points
ID: 40015566
To add to Steve's post above.

Think about what you are doing.

Anything in Oracle inside single quotes is a string:  'this is a string to Oracle'.

So, you have a string 'March 15, 1979'.  What do you want to do with that string?

Convert it to a string or to a date?  Seems pointless to convert a string to a string doesn't it?

TO_CHAR takes a non-string value and converts it to a string.

TO_DATE takes a date value and converts it to a string.
0
 
LVL 4

Author Closing Comment

by:Pancake_Effect
ID: 40015698
Thanks for all the help again, I learned a lot!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question