Solved

SQL: ALTER and UPDATE with one statement?

Posted on 2014-04-22
46
414 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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
Oops just saw your post above, I'll take a look
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
GUI
Which way will make it work in this GUI?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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)
Comment Utility
Excellent point Steve.  I thought of if but forgot to mention it.
0
 
LVL 4

Author Comment

by:Pancake_Effect
Comment Utility
>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)
Comment Utility
>>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
Comment Utility
It's just the free version I'm practicing on right now, "Oracle Database Express Edition"
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
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 22

Expert Comment

by:Steve Wales
Comment Utility
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
Comment Utility
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)
Comment Utility
>> 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
Comment Utility
>>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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
haha that was it xD

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

Author Comment

by:Pancake_Effect
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How can I use listagg with distinct. 4 72
Space Delimited Sql File 4 70
report returning null 21 49
dates - loop 12 38
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now