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
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
Blog Comments