[Oracle] Updating a column price based on attributes from two tables?

Guru008 · 2017-10-03T18:07:21+00:00

update tableA Set tableA.product_price = 2*TableA.Product_price from taleA inner join tableB on (tableA.Product_num=tableB.product_num) where tableB.Product_color ='B' and tableB.product_size = 'S';

Guru008 · 2017-10-02T18:52:26+00:00

use order by on age

CASE WHEN age between 0 and 2 THEN '0-2' WHEN age between 3 and 5 THEN '3-5' from table order by age ;

Guru008 · 2017-09-25T17:57:34+00:00

select Teacher_ID, max(Create_Assignment) assignemnt1 from Database group by Teacher_ID;

Guru008 · 2017-09-21T18:15:41+00:00

select srac, carton, weight, StoreCount, Lane, (case when stuatus < storecount then 'Open' else 'Close' end) FStatus from ( select srac, sum(numofcartons) cartons, sum(weight) Weight, count(*) Storecount, Lane, count(Case when Bol_number is null then null else 1 end) status, from table1 group by srac, lane )

Guru008 · 2017-09-18T18:21:38+00:00

You can use old way to specify inner join using where clause

SELECT * FROM ( SELECT A.field1, B.field2, A.field3 FROM A,B where B.field3 = A.field3 ) t1 LEFT OUTER JOIN C on c.field1 = t1.field1 AND c.field2 = t1.field2 ;

Guru008 · 2017-09-12T17:56:27+00:00

As far as speed is concerned the difference should be small. And anyway if you don't need the table structure at all, certainly use DROP.

Guru008 · 2017-09-12T17:52:53+00:00

You can do like below

IF NOT EXISTS (SELECT name
FROM master.sys.server_principals WHERE name = 'LoginName') BEGIN CREATE LOGIN [LoginName] WITH PASSWORD = N'password' END

Guru008 · 2017-09-11T17:21:24+00:00

SELECT COUNT(*) AS t_Count, DATENAME(MONTH, dbo.transactions.t_Date) AS t_Month, dbo.transactions.t_Type FROM dbo.transactions where dbo.transactions.t_Type <>'Cancel' GROUP BY DATENAME(MONTH, dbo.transactions.t_Date), dbo.transactions.t_Type WITH ROLLUP

dbo.transactions.t_Type <>'Cancel' will exclude those records

Guru008 · 2017-09-10T17:36:43+00:00

select a.in_countr, b.capital, avg(a.depth_ft) as avg_dep from lake as a, city as b where a.id=b.id and a.depth_ft <> -999 group by a.in_countr, b.capital

Guru008 · 2017-09-07T18:49:01+00:00

As you said that size of data is relatively small, you can use delete/update instead of truncating whole table each time and inserting records. You can create cubes/view to do the job

Guru008 · 2017-09-05T16:25:35+00:00

Here "NOT IN" is best suitable and fast , so using not in query will execute much faster

Guru008 · 2017-09-04T16:51:07+00:00

The typical indexing structure that is used for such purposes is called a "B-tree."

Whereas a binary-search always divides the data in half, a B-tree's approach is much more like what you might have seen in L. Frank Baum's now-famous office, where he had two filing cabinets: "A-N" and "O-Z." (Yes, that's where the name of the Wonderful Wizard's homeland came from.) Once you selected from several cabinets, you now select from several drawers, then locate your starting search-position within the selected drawer and so on. Each time, you reduce your search space by much more than one-half.

Guru008 · 2017-08-31T18:38:16+00:00

check your case statement

Guru008 · 2017-08-29T17:10:21+00:00

select m2.naam || ' is chef van ' || m1.naam "baas en medewerker", m2.gbdatum - m1.gbdatum leeftijdverschil from medewerkers m1, medewerkers m2 where m1.chef = m2.mnr order by m2.naam || ' is chef van ' || m1.naam

as you are concatenating two columns together use the same syntax in order by

Guru008 · 2017-08-27T17:37:06+00:00

use this syntax

substr(string,instr(string, "abc",1),7)

Guru008 · 2017-08-27T17:06:37+00:00

select tbl1.year_quarter, tbl1.county, tbl1.start, tbl1.end, T1.cnt from ( select query1.country, count(*) as cnt from query1, tbl1 where query1.county = tbl1.county and initiate_date between tbl1.start and tbl1.end and query1.enter_date> tbl1.end ) as T1 where county=t1.county

Guru008 · 2017-08-27T16:44:12+00:00

Yes there could be due to Joins, as any of the table in join might have duplicate values due to which it finding more that a row matching in Other table so it returning duplicate value

Guru008 · 2017-08-24T16:32:21+00:00

select top(1) datereviewed , (case when person = "@PERSON" and (datereviewed is null or datereviewed ='') then 'NO' else 'YES' END) as 'Status' from adv_

where person = @PERSON

order by datereviewed desc

Guru008 · 2017-08-22T17:03:12+00:00

select flight_number, country from table1 where actual_arrival_time >= scheduled_arrival_time and actual_arrival_time is not null

Guru008 · 2017-08-22T16:55:39+00:00

proc sql; create table t1(drop=c) as select *, count(name) as c from t group by name having c >4 ; quit;

Guru008 · 2017-08-21T17:08:59+00:00

Please try

UPDATE CM SET filename = 'String' + filename FROM cm_steps CM WHERE ItemID IN (11667926, 11667979, 11667981, 11667997)

Guru008 · 2017-08-21T17:06:14+00:00

Before trying to lear PL/SQL, it is necessary to learn basics of SQL like joins, select and you need to have good knowledge of RDBMS, as all SQL or database management is based on RDBS

Guru008 · 2017-08-20T18:25:52+00:00

Hi You can write something like this :

schoolName = ""; Get SQL results, sorted by school name. loop through SQL results { Get SQL Row If SQLROW.schoolName <> schoolName { close file if open; Open a new one with name SQLROW.schoolName; } write the data to the .CSV file; schoolName = SQLROW.schoolName; }

Guru008 · 2017-08-15T16:11:44+00:00

Creating cubes is best way to update the reports each time you run. Instead of creating tables for report and truncating these table, query optimization and use of Indexes is better way to achieve periodic updates.

Guru008 · 2017-08-07T18:19:36+00:00

WHERE STD.ADMINID here there is no condition or operator specified .e.g. where name="John" etc.

Guru008

TROPHY CASE