| Author |
drop table if exists
|
|
| Daigo Moriwaki 2005-11-11, 7:23 am |
| Whishlist
New syntax for DROP statement: DROP TABLE table-name IF EXISTS
If the table exists the table is dropped, which suppresses errors and eliminates
user's checking before dropping.
This statement is same as MySQL's.
Thanks,
Daigo
--
Daigo Moriwaki
iAnywhere Solutions K.K. [Tokyo]
daigo.moriwaki at ianywhere dot com
| |
| krisztian pinter 2005-11-11, 7:23 am |
| On 11 Nov 2005 02:14:03 -0800, Daigo Moriwaki
<daigo.moriwaki@ianywhere.com> wrote:
> New syntax for DROP statement: DROP TABLE table-name IF EXISTS
it would be more flexible to have
IF EXISTS TABLE tablename THEN
...
END IF;
| |
| Breck Carter [TeamSybase] 2005-11-11, 9:23 am |
| Personally, I like the Oracle CREATE OR REPLACE syntax although it
does not apply to tables.
The following works reasonably well, and is easier to code than the IF
EXISTS ... SYSTABLE alternative:
BEGIN
DROP TABLE ttt;
EXCEPTION WHEN OTHERS THEN
END;
Of course, there are more than one reason a DROP can fail; e.g., if
someone else has a shared table lock. Then the CREATE will still fail.
Breck
On 11 Nov 2005 02:14:03 -0800, Daigo Moriwaki
<daigo.moriwaki@ianywhere.com> wrote:
>Whishlist
>
>New syntax for DROP statement: DROP TABLE table-name IF EXISTS
>
>If the table exists the table is dropped, which suppresses errors and eliminates
>user's checking before dropping.
>
>This statement is same as MySQL's.
>
>
>Thanks,
>Daigo
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| David Fishburn 2005-11-11, 9:23 am |
| "krisztian pinter" <pinterkr@freemail.hu> wrote in
news:opsz2m7ojdwwfeh
v@karwst_pint
of sybase.public.sqlanywhere. product_futures_disc
ussion:
kp> On 11 Nov 2005 02:14:03 -0800, Daigo Moriwaki
kp> <daigo.moriwaki@ianywhere.com> wrote:
kp>
kp>
kp>> New syntax for DROP statement: DROP TABLE table-name IF EXISTS
kp>
kp> it would be more flexible to have
kp>
kp> IF EXISTS TABLE tablename THEN
kp> ...
kp> END IF;
I use this format personally.
IF EXISTS( SELECT 1
FROM sys.systable st KEY JOIN sys.sysuserperms sup
WHERE st.table_name = 'replace_me'
AND sup.user_name = user_name() ) THEN
DROP TABLE replace_me;
END IF;
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).
EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm
Developer Community / Whitepapers
http://www.ianywhere.com/developer
CaseXpress - to report bugs
http://casexpress.sybase.com
CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
| |
| krisztian pinter 2005-11-11, 9:23 am |
| On 11 Nov 2005 06:33:03 -0800, David Fishburn =
<fishburn_spam@off.ianywhere.com> wrote:
> I use this format personally.
>
> IF EXISTS( SELECT 1
> FROM sys.systable st KEY JOIN sys.sysuserperms sup
> WHERE st.table_name =3D 'replace_me'
> AND sup.user_name =3D user_name() ) THEN
so do we, or some similar.
| |
| Dan Konigsbach 2005-11-18, 8:23 pm |
| The original poster *said* that adding the option IF EXISTS "eliminates
user's checking before dropping".
Pointing out that you know how to code the test that the poster says
could be eliminated proves, um, what?
Yes, you can code
IF EXISTS( SELECT 1
FROM sys.systable st KEY JOIN sys.sysuserperms sup
WHERE st.table_name = '<tablename>'
AND sup.user_name = '<username>' ) THEN
instead of
IF EXISTS
Is that preferable? Does it add any kind of robustness to your SQL?
Is it cleaner? Safer? Does it verify consistency in some useful way?
Is it less likely to be coded incorrectly?
Or is it an inconvenience that people have lived with so long that they
have become proud of the fact that they know about it and know how to
work around it? (I refer to that as the "strncpy syndrome".)
I don't think so. I think that this is typical of a whole category of
schema manipulation operations that could safely and cleanly be made
idempotent.
I think it is a great idea.
Dan
| |
| Dan Konigsbach 2005-11-18, 8:23 pm |
| The original poster *said* that adding the option IF EXISTS "eliminates
user's checking before dropping".
Posting how to code the test that the poster says could be eliminated
proves, um, what?
Yes, you can code
IF EXISTS( SELECT 1
FROM sys.systable st KEY JOIN sys.sysuserperms sup
WHERE st.table_name = '<tablename>'
AND sup.user_name = '<username>' ) THEN
instead of
IF EXISTS
Is that preferable? Does it add any kind of robustness to your SQL?
Is it cleaner? Safer? Does it verify consistency in some useful way?
Is it less likely to be coded incorrectly?
Or is it an inconvenience that people have lived with so long that they
have become proud of the fact that they know about it and know how to
work around it? (I refer to that as the "strncpy syndrome".)
I think that this is typical of a whole category of schema manipulation
operations that could safely and cleanly be made idempotent.
I think IF EXIST, or something like it, is a great idea.
Dan
| |
| Pavel Karady 2005-12-27, 11:23 am |
|
"krisztian pinter" <pinterkr@freemail.hu> wrote in message
news:opsz20w2jcwwfeh
v@karwst_pint...
On 11 Nov 2005 06:33:03 -0800, David Fishburn
<fishburn_spam@off.ianywhere.com> wrote:
> I use this format personally.
>
> IF EXISTS( SELECT 1
> FROM sys.systable st KEY JOIN sys.sysuserperms sup
> WHERE st.table_name = 'replace_me'
> AND sup.user_name = user_name() ) THEN
We do so also, but with one more AND: st.table_type='BASE'
Pavel
|
|
|
|