SQL & PL/SQL :: Join Update Clause

Jun 9, 2010

I want to use join condition in update syntax.Like the following way but it doesnot work.how to fix it.

update tab_1 a
set a.qty = b.sell_qty
from tab_2 b
where a.nbr=b.nbr

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Inner Join Used Using Clause?

Apr 11, 2012

Equi join (Inner join)

It is the simplest join or inner. An equijoin combines rows that have equivalent values for the specified columns.

SQL> select * from x;

NAME EMAIL EMPID
Sam email@removed 1060
Rose email@removed 1061

[code]....

don't consider above mentioned queries I got valuable outputs.

NAMEEMAIL EMPID NAME EMAIL EMPID
samemail@removed 1060 sam email@removed 1060
roseemail@removed 1061 rose email@removed 1061
sonaemail@removed 1062 sona email@removed 1062

Inner join shows matches only when they exist in both tables.so , i got records 1060,1061,1062

// Referencing columns used in a USING clause.
SQL> select x.name,x.email,x.empid from x
2 inner join y
3 using (empid);

select x.name,x.email,x.empid from x
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

so query rewritten as

SQL> select x.name,x.email,empid from x
2 inner join y
3 using (empid);
NAME EMAIL EMPID
--------------- --------------- ----------
sam email@removed 1060
rose email@removed 1061
chris email@removed 1062

I mean see two different outputs.first output records twice displayed ... Yes i agree that is Inner join.second output records not displayed twice... common records only displayed once ,in x and y.

I think should n't use a table name or alias when referencing columns used in a USING clause... am i right ????

I want to know both are inner joins .how Oracle is determined both outputs ?

View 13 Replies View Related

SQL & PL/SQL :: Update Statement With Join Only Update Matching Rows

Aug 17, 2010

This is my query:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c

[Code]...

The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds

[Code]...

but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.

View 9 Replies View Related

Inner Join On Clause Statement?

Jul 21, 2011

I have 2 sql's statement below, and just wondering if their is difference between the two sql's.

FIELDS data type:
--------------------
a.field is DATE
b.field is also a DATE

SQL1:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field = b.field
WHERE a.field between b.field AND b.field + 2
;

SQL2:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field between b.field AND b.field + 2
;

OR

SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field >= b.field AND
a.field <= (b.field + 2)
;

which ever is correct between the two sql.

QUESTION: would be the two sql's generate same result set.

View 1 Replies View Related

Natural Join With ON Clause?

Apr 22, 2012

CODESQL> select * from em1;

EMPID NAME SALARY
---------- ---------- ----------
1060 sam 4000
1061 rose 3700
1062 sona 4800

SQL> select * from dept;

EMPID NAME DEPT_NAME
---------- ---------- --------------
1060 sam INFO TECH
1061 rose BIO INFO
1063 chris COMP SCI
1064 maya MULTI MEDIA

I am TRYING to get output for on clause( NATURAL JOIN)

CODESQL> select x.empid,x.name,x.salary,y.dept_name from em1 x NATURAL JOIN dept y
2 on x.empid=y.empid;
on x.empid=y.empid
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

[code]...

My questions are

** I think why NATURAL JOIN key word throws error.
** Second query succeed. i think it is inner join. am i right ??????
** If i execute query without alias why oracle throws error ???? Example shown below

I saw lot of examples like this
SQL> select empid,name,salary,dept_name from em1 natural join dept
2 on em1.empid=dept.empid;
on em1.empid=dept.empid
*
ERROR at line 2:

ORA-00933: SQL command not properly ended

View 1 Replies View Related

SQL & PL/SQL :: Join Syntax In From Clause

Jul 21, 2010

I have this ORACLE SQL and just about understand join syntax in the From clause, i.e.

SELECT *
FROM TABLE 1 LEFT OUTER JOIN TABLE 2
ON TABLE1.FIELD_X = TABLE2.FIELD_X

However, I've inherited the sql below.

SELECT
RELOCATION.START_DATE,
STUDENT.SURNAME,
STUDENT.FORENAME
CURRENT_SCHOOL.BASE_ID
RELOCATIONS.STUD_ID
[code]......

I particularly don't understand this part

' FROM (MYDATABASE.STUDENT STUDENT
LEFT OUTER JOIN MYDATABASE.BASES CURRENT_SCHOOL '

why the table name student is referenced twice?And again for ' MYDATABASE.BASES CURRENT SCHOOL '?

When I put this into SSRS it shows only links between the tables STUDENT, RELCOATIONS and CURRENT_SCHOOL. Bases isn't mentioned in the tables diagram. it is still referred to in the raw SQL.

The above SQL works fine, i just don't understand what it's doing!

View 3 Replies View Related

PL/SQL :: Difference - In Conditions (Join And Where Clause)

Sep 19, 2013

I need to be clear about what exactly difference when we put any condition in INNER JOIN and WHERE Clause. I tried both way and found same results. Even in Statistics Plan not much differences. 

1. Here I am using location filter in Inner join condition -

"SELECT I.*, Gl * From Sc1.Item I   Inner Join Sc1.Part P  On P.Part_Id = I.Part_Id       Inner Join Sc1.Location Gl  On Gl.Location_Id = I.Location_Id   And Gl.Location_Id In ( 1767, 1747,202,1625)    Inner Join Sc1.Condition C On C.Condtion_Id = Gl.Condition_Id Where  I.Inactive_Ind = 0  And I.Condition_Id != 325         

2. Here I am using location filter in Where clause

SELECT I.*, Gl * From Sc1.Item I   Inner Join Sc1.Part P  On P.Part_Id = I.Part_Id       Inner Join Sc1.Location Gl   On Gl.Location_Id = I.Location_Id   Inner Join Sc1.Condition C        On C.Condtion_Id = Gl.Condition_Id Where  I.Inactive_Ind = 0       and I.LOCATION_ID in ( 1767, 1747,202,1625)    And I.Condition_Id != 325.

View 23 Replies View Related

SQL & PL/SQL :: Using Update With A Join?

Mar 16, 2011

im trying to update a column in the employee table with the value "YES". Im getting an error message saying im missing a SET statement from this code below:

update e
SET e.review='YES'
from employee
inner join rentals r
on e.employee_id=r.employee_id
inner join job j
on e.job_id=j.job_id
where r.plate ='FY06WNT'
and j.function !='MANAGER'
and j.function !='PRESIDENT';

View 2 Replies View Related

SQL & PL/SQL :: How To Update With Join

Jul 7, 2010

drop table dev10 purge
/
drop table dev11 purge
/
drop table dev12 purge
/
create table dev10 as
select rownum c1, sys.dbms_random.string('U', 6) c2, trunc(sys.dbms_random.value(1, 7)) c3
from dual connect by rownum < 8
/

[Code]...

Now, Let us assume that, the record is

AAAAAA BBBBBB CCCCCC
DDDDDD EEEEEE FFFFFF
...
..
.

Now, we want dev12.c2 is 'FFFFFF' if dev11.c2 is 'BBBBBB', if we want to get this:

AAAAAA BBBBBB FFFFFF
DDDDDD EEEEEE FFFFFF
...
..
.

We can make this for SqlServer by coding:

UPDATE dev10
SET c3 = dev12.c1 FROM dev10 INNER JOIN dev11 ON dev11.c3 = dev10.c1 CROSS JOIN dev12
WHERE (dev11.c2 LIKE 'BBBBBB')
AND (dev12.c2 LIKE 'FFFFFF')
/
but, Oracle, what should we do new?

View 11 Replies View Related

Update With Join

Apr 13, 2011

This is my working query in ms access...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..

translate this query to work on oracle?

View 5 Replies View Related

SQL & PL/SQL :: Use FROM Clause In Update Command?

Jul 13, 2011

Can we use FROM clause in update command?

View 2 Replies View Related

SQL & PL/SQL :: Update With Join Over Two Tables

Apr 28, 2010

I am trying to write an Update that really frustrates me because it won't work for one reason or another.The situation is that I have two tables for customer information, t1 with the names of the customer and t2 with the address.These two can be joined via a client_id.

Now I have a third table t3 with the name and address of potential customers. I want to find out if some of them are already known to me so that I can update the client_id from table t1 or t2 into t3.

I have to join firstname, lastname from t3 to firstname, lastname from t1 and street, zip, city from t3 to street, zip, city from t2 and client_id from t1 to t2. Additional there is the problem that there can be more than one result so I have to update one of the found client_ids per name/address into t3.I am no expert to PL/SQL, I just know what SQL works in Access and that is:

UPDATE (t3 INNER JOIN t1
ON (t3.firstname= t1.firstname) AND (t3.lastname = t1.lastname)) INNER JOIN t2
ON (t3.city = t2.city) AND (t3.zip = t2.zip) AND (t3.street = t2.street) AND (t1.client_id = t2.client_id)
SET t3.client_id = t1.client_id;

View 2 Replies View Related

SQL & PL/SQL :: Update In Join Condition

Feb 16, 2011

I Require to Update the Data in Join Condition. When Run the Query the Error display as ORA-00933: SQL Command Not Properly Ended.

Query:

Update a set a.Dr_Re = Nvl(b.Dr_Amt,0),
a.Cr_Re = Nvl(b.Cr_Amt,0)
FROM xxsc.xxsc_creditors_aging_brnwise a
join (Select Branch,Invoice_id,vendor_site_code,segment1,
case when (sum(nvl(dr_re,0)) - sum(nvl(cr_re,0)) > 0) then sum(nvl(dr_re,0)) - sum(nvl(cr_re,0))
Else 0 End DR_AMT,
[code]........

View 2 Replies View Related

SQL & PL/SQL :: Update Query Using Join

Jun 26, 2012

How to update single table column using join query

Example:

Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1

View 8 Replies View Related

SQL & PL/SQL :: Update Join View?

May 20, 2010

9i worked fine 11g release2 giving ora-01779

alternative sql for the following :

UPDATE /*+ BYPASS_UJVC */
(
SELECT
c.c1,
c.c2,
c.c3,

[code].....

View 5 Replies View Related

With Clause - Use Values Of Cteas In Update?

May 2, 2013

with cteas
as
(
select hit from radial,gib
where no=id
)
select hit from cteas

[code]...

for the above query can i use the values of cteas in update .because it throws error cteas as table/view not found

if i use like this am getting error in update (missing select key word)

with cteas
as
(
select hit from radial,gib
where no=id
)
update xenon
set nub=(select hit
from cteas
where ren=hit)

View 3 Replies View Related

SQL & PL/SQL :: Update Table With Primary Key In Where Clause

Dec 11, 2012

Is is required to check the number of rows updated in a table when the primary key of the table is used in the filter criteria of the update statement? As I know,by default it will update only one record. But if it happens to be an important transaction table and only one record is required to be updated, then is it the best practice to use the 'SQL%ROWCOUNT' check in the query, even if the update query is using primary key in filter clause.

Example:Consider Trans table with trans_id as primary key. Then:
Update Trans
set trans_status='pass'
where trans_id=123;

I know this will update only one record. But what is the best practice? Shall I use 'SQL%ROWCOUNT' after this update to double check whether the record is updated or not?

View 7 Replies View Related

SQL Code For Update Using Join Conditions?

Jan 8, 2011

novice to SQL (Oracle 10g)

Am trying to write code for sollowing scenario:

Have 3 tables
table1 (campaignid,promoflag)
table2 (campaignid,projectid,campaigndesc)
table3 (projectid,promoflag,projectstart,projectend)

I am to update table 1 promoflag with value from promoflag in table3

Update table1
set promoflag = table3.promoflag

I would like to make sure only appropriate record is updated therefore want to use where clause condition but the primary key for table1 and table3 are different, the only link can be found on table2.

I want to use condition where table1.campaignid=table2.campaignid and table2.projectid=table1.projectid

Have used the following without success:

Scenario 1

Update table1
SET promoflag = table3.promoflag
FROM table1
inner join table2 on table1.campaignid = table2.camapaignid
inner join table3 on table2.projectid = table3.projectID;

Error at line 1 ORA-00933: SQL command not properly ended

Scenario 2 with real table/column names

Update UA_CAMPAIGNEXTATTR
SET CFPROMOTABLE = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE
FROM UA_CAMPAIGNEXTATTR,UA_CAMPAIGN,LMUK_PROJECT_AUDIENCE_GRID
WHERE
UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID
AND UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID;
Error at line 2
ORA-00933: SQL command not properly ended

It appears to get block with the 'FROM' statement (was underlined in red)

View 1 Replies View Related

Update With Join Syntax In Oracle

Apr 13, 2011

This is my working query in ms access...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..translate this query to work on oracle?

View 4 Replies View Related

PL/SQL :: Update Statement In Join Query

Nov 16, 2012

I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:

UPDATE tbl1
SET tbl1.EMPID =
(SELECT  tbl2.EMPIDA  FROM tbl2 
WHERE LOWER(tbl1.EMAILCOL) =  LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE  tbl1.EMAILCOL= tbl2.EMAILCOL2 );

But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.

View 12 Replies View Related

Update With Case Clause Auto Commits

Nov 26, 2012

i tried the following update on one table:

update siebel.s_contact
set marital_stat_cd =
case
when (marital_stat_cd = 'Casado') then 'Married'
when (marital_stat_cd = 'Solteiro') then 'Single'
when (marital_stat_cd = 'Divorciado') then 'Divorced'
end

As you can see i forgot the else, so my update is wrong.

I thought i could rollback the update issuing the rollback statement, but when i have issue the rollback, the i query the table to confirm that the update was rollbacked and for my suprise the update is commited.

I didn�t issue the commit statement after the update and i confirmed that the auto-commit feature to worksheets is disabled, so i don�t understand whit the update was commited.

View 5 Replies View Related

WHERE Clause Of Some Update Statements Not Using Primary Keys

Feb 3, 2009

Inside procedure,I need to validate all the columns (Ex:col1 should not accept more than 40 chars) and update status(containing error or not) of these columns into another column in the same table.For this,I mentioned only 'UPDATE' statements.So 'WHERE' clause of some update statements not using Primary keys.

In that table composite primary key was created.This procedure is successfully complied & executed now.But this procedure took more than 10 mins to execute.I need to reduce the time to less than a min.

View 8 Replies View Related

Lock Child Table By FOR Update Clause?

May 7, 2013

I am using the Oracle 10g and I have question related to "for Update" clause.We have the data warehouse db, so no foreign key constraint between parent and child.We process the data files every hour, the condition is If we find the row in parent table then we go and look into child tables and perform insertion (if no corresponding record is present) or updation (if one corresponding record is present) in the child table.

The problem is If I run the two process simultaneously for the same kind of data, and if no record is present in the child table then it create the duplicate in child table.My question is if I use FOR Update clause while selecting the data in parent table will it lock the child table for any insertion or updation?

Ex- We have employee table for employee 1

In my data files I have the row for employee 1, so when I run the select query on employee table I found 1 row.The I look the child table "Salary" as there is no record for emp_id =1 in this table I insert the record for this

Emp_id Salary
1 500

The problem is if both the process run at same time then I get duplicate rows in child table

Emp_id Salary
1 500
1 500

we do not want the duplicate row insertion. Can I lock the child table during first process run

View 4 Replies View Related

SQL & PL/SQL :: Update Query Based On Join Condition

Jun 16, 2011

I have two tables. By joining these two tables, I need to update a field in table1.

UPDATE table1
SET table1.FLAG = 'Fixed'
where table2.lastname = table1.lastname
and table2.status in ('fulltime','parttime')

I keep getting error 'table1.lastname' is invalid identifier.

I can't understand the error message. I made sure that the fields exist.

View 5 Replies View Related

Forms :: Update Record In Join Condition Block?

Jun 13, 2012

i want to update record that is fetched based on join condition on form

1. made a block manually :::: EMPSAL
2. Query DATA SOURCE NAME :::: EMP a, Sal b
3. Where Clause :::: a.empid = b.empid
4. DML DATA Target Type :::: Table
5. DML DATA Target Name :::: EMP a, Sal b
6. All Columns are marked a.empid, a.empname, b.sal, b.date etc

It does not allow me to update record.

View 1 Replies View Related

SQL & PL/SQL :: Update Table With Join / ORA-00933 / Command Not Properly Ended

Jan 18, 2013

I'm unable to get the below update SQL to run in Oracle, it's giving me th below error

ORA-00933: SQL command not properly ended.

UPDATE
PDR.PH_Family_Match_by_Chassis a
SET a.Launched = 'Y'
INNER JOIN
PDR.domCHASSIS
ON
a.chassis_id = PDR.domCHASSIS.chassis_id

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Normal Join And Outer Join

Oct 19, 2013

Lets say I have three tables t1 and t2 and t3.

SELECT * FROM T1;

Id
____
1
2
3
4

SELECT * FROM T2;

Id
____
1

SELECT * FROM T3;

Id
____
1

Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join

select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id

However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4

One way of doing that is using the not exists clause

select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)

Is there a better way of doing this in sql ?

View 5 Replies View Related

SQL & PL/SQL :: OrderBy Clause Before From Clause?

Apr 23, 2010

can we use something like this

"select ... order by emp from emp"

what is to be done? so that this qurey runs. no co-related subquery to be used.

View 6 Replies View Related

SQL & PL/SQL :: Left Outer Join Versus Right Outer Join

Aug 14, 2009

i want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?

View 6 Replies View Related

Forms :: Insert And Update Directly Into Table From Pre Update Trigger Of Block?

May 14, 2010

I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:

If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.

Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved