Published on
January 16, 2009 in
Database.
This is a simple one SQL I used yesterday for the Oracle application development. I have two tables which have two common fields (let us say, table_id and col1). The col1 in table1 is empty since it is a newly created table, and the col1 in table2 has the data but I will not use table2 since it is a old table. What I need to do is to move all col1 data from table2 to table1. The challenge is Table1 and Table2 do not have the exact same data on table_id.
First I used the following SQL statement to update the col1 in the table1 from column value in table2:
update table1
set col1=(select col1 from table2 where table2.table_id=table1.table_id)
Continue reading ‘How to update one table from values in another table’
Related posts
Published on
January 10, 2009 in
Database.
I have a Oracle database table with some fields (columns) in numeric format. Now I need to update one column, namely store_id, to make it a primary column with unique number id. Since there were a lot of duplicate data in this column, the best way to do is to update this column with the sequence numbers.
To accomplish this, I need to create a sequence object in Oracle, and use Oracle cursor to loop and update the table column.
Here is the PL/SQL I used and worked.
DECLARE
Cursor store_id
IS
SELECT store_id FROM store_locations FOR UPDATE;
BEGIN
FOR c_store_id IN store_id LOOP
UPDATE cms_store_locations
SET store_id = store_seq.nextval
WHERE CURRENT OF store_id;
END LOOP;
commit;
END;
/
Related posts
Published on
December 19, 2008 in
Database.
Recently just re-installed the Microsoft Office SharePoint Server (MOSS) 2007 applications and database on new servers. But the SharePoint data files were in the C: system drive, which does not have enough space.
To move database files to the D: data drive, what I needed to do is
- Remove Content Database from the SharePoint Administrative Central.
- Deattch the database from Microsoft SQl server
- Move data files to the D: drive
- Re-attach the database
- Add Content Database to Web Application.
Related posts
Published on
September 23, 2008 in
Database.
I have tried to re-configure the old web site log analyst tools – LiveStats XSP 6.2 for a long, long time. Finally I decided to wipe all of them and re-install again. To be safe, I have to back-up all MySql data (installed by LiveStats) first. Since the server is a Windows 2000, and I could not use web interface like Phpmyadmin. Well, I can copy all *.MYD files. But I found a better way by using the the following commands came with MySQL installation.
Continue reading ‘How to export and import MySql database’
Related posts
Published on
August 27, 2008 in
Database.
In today’s Oracle 10g training, we were asked to do a exercise on tablespace. I needed to make a tablespace Read Only and Read Write. The syntax was simple, just “ALTER TABLESPACE tbs READ ONLY (READ WRITE”. But while I was playing around, I thought what would happen on the data file after I turned on the Read Only with the tablespace. So I checked the data file (like c:\oracle\oradata\something.dbf) and found out it did not have Read-only attribute even after I altered the tablespace as Read Only. Interesting, it seems the Oracle did not look up the OS level when it deals with the tablespace read-only attributes.
To make things more interesting, I turned on the Read-Only attribute for this tablespace’s data file (Right Click, and check the Read-Only in the proprieties window). Then I went ahead to make more alterations on the tablespace, like increasing the extents size etc. The funny thing was I could see these system changes in Oracle through some dba_ views, but actually they did not take effective. Since I could not insert any new record to a full tablespace even I “successfully” increase the size in Oracle.
Not sure Oracle already aware this situation or not.
Related posts
Published on
August 7, 2008 in
Database.
Challenge:
Let me assume this: we have two tables, one is Regions table with all region information (East, West, etc. ) and we have another table with Sales information. Now, we need to display a report with region information and how many sales conducted in each region with greater than $1000 value in each sale. It sounds simple at the beginning since a Count(*) with Group By can do this trick.
But what if I need to still show the region which does not have any sales greater than $1000 on the report? Using Count(*) itself in the query, the region without any sales greater than $1000 will not show in the query result, period.
Continue reading ‘How to display 0 in conditional [group by] report in Count() SQL Query’
Related posts
Challenge: By default, the SQL Server Express 2005 was not installed for remote access. Which means networking protocols are disabled by default in SQL Server Express. Thus, if you simply installed Express and chose all the defaults like I did, SQL Server Express will only be able to have connections originating on the local machine where SQL Server is installed.
That is why I got the following error message when I tried to connect to one SQL Server Express instance installed on another server from my desktop’s SQL Server Management Studio Express.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
And this is the screen shot I have.
Continue reading ‘How to remote connect to Microsoft SQL Server Express 2005′
Related posts
Ok, finished the installation of Oracle 9i on the new machine. It is time for me to move all existing data from my old machine to this new server. I do not want to create all stuffs from the scratch, and do not want to use exp/imp utility either. I thought there should be a easy way to do so. Since I am a web designer, I will give such question to the DBA in my team.
It turns out there is an easy way, yeah!
Here are some steps I did to move all data from the old machine to new machine with the help of my DBA co-worker. Continue reading ‘How easily to move Oracle data from one machine to another’
Related posts
Today I tried to install Oracle 9i on my Windows 2003 Web Edition server with 3 installation CDs. First I did not get the auto-run working when the disc 1 inserted. So I browsed to the CD driver and clicked the autorun.exe file under the autorun folder manually. But I got an error message window indicating that “Windows cannot open this program since it has been disabled“.
This is weird. I thought it might be my CD-ROM problem, so I move these files to my hard-disk. Re-clicked again, the same error. I tried the setup.exe too, and got the same error. Tried the same CD on the Windows XP pro machine, ran without any problem.
Continue reading ‘“Windows cannot open this program since it has been disabled” error message when I try to install the Oracle9i Release 2 on my Windows Server 2003, Web Edition-based computer’
Related posts
Blog Comments