Update Using Join Conditions

Apr 7, 2008

I have three table For example
Employee (Empid , Empname , Esal)
Department (Deptid , Deptname , empid )
Staff (staffid , Staffname , Empid)

It is just example
how can i update Empname whose staffid =1 accor to staffid)
using Join Conditions :- Pls help me out ..
or
how to update data using JOIN Conditions







Yaman

View 2 Replies


ADVERTISEMENT

JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions

Jan 10, 2008

My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:


SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)


No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.

--Version 1:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE

UserRoles.Active = 'TRUE'


-- Version 2

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId

AND UserRoles.Active = 'TRUE')


So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.

So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?

Thanks for whatever information you can provide.

View 7 Replies View Related

Can We Put 2 Conditions In Inner Join

Oct 24, 2006

pls:
1/ can we do it this way:
inner join Table2 ON table1.fld1=table2.fld21 AND table1.fld12=table2.fld22
2/also:
what s the difference between join , iner join and left join
Thanks .

View 4 Replies View Related

Left Outer Join With Two Conditions

Feb 11, 2008

Dear All,
I am not sure this is the correct forum for my issue, still im going on.....
I have a sql query which uses left outer join and where clause
select I.ir_id, I.ir_title,I. ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' order by I.ir_id desc
this works perfect. Now I want to exclude few records from the result on condn 'bstatus <> 'Yes'.  So now my query will be
select I.ir_id, I.ir_title, I.ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR  I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' and B.bstatus <> 'Yes'order by I.ir_id desc
The below query will not return the left table records which doesnt have a matching one in the right table. It works something like inner join.
Could you please anyone tell me,where I am wrong?
Thanks,
Girija

View 3 Replies View Related

Left Outer Join And Conditions

Mar 17, 2008

I am using Transact-Sql 2005, and I'm trying to do a left outer join, including only certain accounts. The account number is x-xxx-xxxx-xxxx.
I want to include only accounts where the last 4 digits are > 7149, and the first 5 digits are between 2-110 and 2-999, or are equal to 8-001.

This is my code, based on 2 temporary tables I have previously populated:

select
b.gl7accountsid,
b.accountnumber,
t.description,
t.category,
t.postdate,
t.poststatus,
t.transactiontype,
t.transamount,
coalesce(t.transamount,0) as TransactionAmount,
t.encumbrancestatus,
t.gl7fiscalperiodsid,
b.budamount,
b.gl7budgetscenariosid

from
#budgets b
left outer
join
#transactions t
on t.accountnumber=b.accountnumber
And right(t.accountnumber,4) > 7149
and (left(t.accountnumber,5) between '2-110' and '2-999' or left(t.accountnumber,5)='8-001')
order by b.accountnumber


I keep getting accounts with the last 4 digits > 7149, and also accounts whose numbers don't fall into the desired group of the first 5 digits.

I'm fairly new to SQL, and I'm thinking there must be a way to do what I want to do, but I don't know what it is.

Can anyone help?

Thanks very much ,

Sue

View 5 Replies View Related

Adding Conditions In The ON Clause Of A JOIN

Feb 12, 2008

Hi Faculties,I have two queries which give me the same output.-- Query 1SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.idWHERE cat.id = 1;-- Query 2SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.id AND cat.id = 1;The first query uses the WHERE clause and the second one has all theconditions in the ON clause. Is there anthing wrong with the secondapproach in terms of performance? Please suggest.Thanks in advanceJackal

View 6 Replies View Related

Returning Results Left Outer Join With Conditions

Sep 21, 2006

I have a SELECT Statement that I am using that is pulling from two tables.  There won't always be results in the second table so I made a LEFT OUTER JOIN.  The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value.  I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1
FROM employee LEFT OUTER JOIN
request AS request ON employee.emp_id = request.emp_id
WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))
AS dayspending
FROM employee AS employee_1 LEFT OUTER JOIN
request AS request_1 ON employee_1.emp_id = request_1.emp_id
WHERE (employee_1.emp_id = @emp_id)
GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial,
employee_1.emp_lname 

View 2 Replies View Related

SQL Server 2008 :: Join Another Table But With Select Conditions?

Mar 24, 2015

I have this sql....

Select
DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName,
s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType,
ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode,
ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,

[code]....

however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'

So the stand alone sql could be....

Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc
from appointments
where dbPFStatus = 1 and clientref = 'EPS'
order by dbPatApptTime desc

I'm just not sure how to incorporate that into my sql or whether there is a better way,

View 9 Replies View Related

How To Update Values Using Where Conditions

Dec 27, 2007

Hi All,

I have my table structure as Follows

S.NO INT
S.NAME VARCHAR(50)

now i want to update values where S.NO=10.

How is this possible to writ query.

As per my knowledge sql won't allow dot as in column name in where condition.

Can any one please advice on this.
This is Priority one.

Thanks in advance.

View 3 Replies View Related

I Need A Sp That Will Update A Field Based On Conditions

Dec 28, 2006

I need to update the status of a client when they make a payment of a certine amount. My problem is this, the two pieces of information needed to do this are comming from two tables. For example;
@ClientID Int,
@PmtAmt Money
IF @PmtAmt >= tblSettings.TopAmt THEN
Update tblClients
SET
ClientStatus='High'
WHERE ClientID=@ClientID
ELSE
Update tblClients
SET
ClientStatus='Medium'
WHERE ClientID=@ClientID
ENDIF
How do I do this in a stored procedure? I need to select the TopAmt from the table tblSettings and then update the table tblClients.

View 3 Replies View Related

Transact SQL :: Update Table From Another With Conditions

Dec 2, 2015

I am trying to do a simple update in one sql table from another where a certain condition in both tables are met.I am missing something basic and have tried numerous posted examples, without success.Here is my code (that does not update) :

        opdragaliasnaaminsit.Connection = konneksie
        opdragaliasnaaminsit.CommandText = "UPDATE vyfjaarontledings " & _
        "SET aliasnaam = T2.aliasnaam" & _
        " FROM  vyfjaarontledings T1" & _
        " INNER " & _
        "JOIN blokke T2 " & _
        " ON T1.plaasno = T2.plaasno " & _
         "WHERE T1.plaasno = T2.plaasno"
opdragaliasnaaminsit.ExecuteNonQuery()

I am trying to update aliasnaam in vyfjaarontledings from blokke.

View 10 Replies View Related

Multiple Conditions In An UPDATE Clause

Dec 12, 2007

I have a table (GLTRANS) with thousands of lines.
1 column in the table (ACCNO) has 300 different values which all need to change to a new value.

ie. 11100 all change to 8100
11200 all change to 8200

I know how to do a simple UPDATE
UPDATE GLTRANS
SET ACCNO = '8100'
WHERE ACCNO = '11100'

But how can i combine into 1 script rather than having to continually change this script 300 times??


Thanks
Wilbur

View 6 Replies View Related

How To Do Bulk Update / Insert In A Table With Conditions

Oct 30, 2015

I have Three tables Student,Daily_Attendance_Master and Daily_Attendence_Details.

I want to run sql of insert or update of student attendence(apsent or present) in Daily_Attendence_Details based on Daily_Attendance_Master_Id and Student_Id(from one roll number to another).

If Both are present in table Daily_Attendence_Details then i want to run Updating of attendance from one roll number to another roll number in Daily_Attendence_Details on the basis of Daily_Attendence_Details_Id

And if both or any one is not present i want to run insert of student attendense from  one roll number to another roll number in Daily_Attendence_Details.

I give below the structure of three tables Student,Daily_Attendance_Master and Daily_Attendance_Details.

Student:-
CREATE TABLE [dbo].[Student](
[Student_Id] [bigint] IDENTITY(1,1) NOT NULL,
[Course_Id] [smallint] NULL,
[Class_Id] [int] NULL,
[Batch_Year] [varchar](20) NULL,
[Student_Initials] [varchar](20) NULL,

[Code] ....

View 13 Replies View Related

UPDATE && JOIN (with WHERE)

Jun 30, 2004

Hi all,

Can someone please help me with this update statement?
UPDATE Stats
SET Stats.JobShownInResults = Stats.JobShownInResults + 1

WHERE Jobs.JobID IN
(
SELECT Query that returns IDs from Search criteria
)

From Stats
inner join Jobs
ON Jobs.JobId = Stats.JobID
I'm trying to increment a value in 'Stats' every time a job in 'Jobs' is returned in a search.

Any help much appreciated,

pete

View 5 Replies View Related

Update With A JOIN

Mar 22, 2013

In the last weeks I came to work with SQL Server more closely and - not being used to it - I stumbled over the sematics of an UPDATE statement using a JOIN (something which is not available in e.g. Oracle).I wonder what the difference between these two updates is:

Code:
update foo
set ..
from bar
where bar.fid = foo.id;
and
Code:
update foo
set ...
from foo f1
join bar on bar.fid = f1.id;

In both cases I have an inner join between foo and bar, but in the second one, foo is actually listed twice in the update statement. As far as I can tell, both carry out the same thing - at least with my test data.

View 10 Replies View Related

Update & Inner Join How?

Apr 23, 2008

Best Greetings,

i want to make an update query for a table but the where clause will check a join with other talbe

update table1 inner join table 2 on x.table1=y.table2 and y.table1= y.table2
set z='anything'

or shall it be


update table1
set z='anything'
where x in (select x from table2) and y in (select y from table2)

i dont know how the syntax be any idea plz ,also table 2 it will be derived from table1 in the form of

select x,max(y)
from table1
order by x

View 2 Replies View Related

Update Using Join

Jan 29, 2015

UPDATE sku
set ecomm = 1
from sku
join invt
on sku.style = invt.style
where invt.first_rcvd = '12/22/2014'

I keep getting an error using this query. It keeps failing at the "FROM" portion. What I want to do is update column ECOMM within table SKU if the STYLE has been received on a certain date.

View 4 Replies View Related

Update With Inner Join

Jun 16, 2006

in a table TBL1 I have to set DESCRIPTION for a TYPE1 equal to DESCRIPTION for a TYPE2 where their ID is equal in TBL1 and their key fields appear together in another table TBL2. In english, in TBL1 the description and id are equal but the type is different. a relationship between their key fields is shown in TBL2. Any thoughts on how to write this?

View 6 Replies View Related

Update Using An Inner Join

Jul 6, 2006

I am trying to update a file based on data retrieved from a join and performing a calculation prior to updationg my result field. I end up with "Column qualifier or table B undefined. "

can anyone see what my problem is?

update a
set a.yr2004 = (b.smal + b.smat) * a.qtypre
from commodityf a inner join itmrvb b
on a.i@stid = b.stid
and a.cinbr = b.itnbr

View 7 Replies View Related

Help With Inner Join In Update

Aug 29, 2007

Here is my update statements which doesn't work, can you show me an example.

UPDATE zurnacik_user SET zurnacik_user.usergroupid=15
INNER JOIN zurnacik_userfield
ON zurnacik_user.userid = zurnacik_userfield.userid
WHERE zurnacik_userfield.field5 = "Kadýn"
AND zurnacik_user.usergroupid = 2

=================

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE zurnacik_userfield.field5 = "Kadýn"
AND zurnacik_user.usergroupid = 2
SE' at line 4

=====

I from Turkey, Thank You...

View 2 Replies View Related

Update Using An Inner Join

Jul 23, 2005

Hello,I have two tables (table1 and table2). I want to set a flag in table1 foreach common row with table2. I use the following syntaxUPDATE table1 SET flag='Y' from table1 INNER JOIN table2 on (table1.a =table2.a) AND (table1.b = table2.b)However the situation arises where I may have a row in table2 that matcheswith two or more rows in table1. The requirement is that I only want toset the flag on a single row in table1.

View 3 Replies View Related

UPDATE JOIN TOP 1

Nov 28, 2006

I am trying to update 1 table with the top records from another table foreach record in the first tableUPDATE HPFSLOWMOVINGSET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOINHPFSLOWMOVING ON HPFSLOWMOVING.ITEMNMBR = IV30300.ITEMNMBR ANDHPFSLOWMOVING.LOCNCODE = IV30300.LOCNCODE WHERE DOCTYPE = 4)This updates all records with the same lastrctdate. I need to update eachrecords with the top lastrctdate where the itemnmbr and locncode equals.Thanks for any help you can provide!Darren

View 4 Replies View Related

Update With Inner Join

Nov 15, 2006

I have an MS Access query that needs to be converted ot SQL Server 2005

Access Query:
UPDATE tblCheckNumber INNER JOIN tblHistory ON
tblCheckNumber.Autonumber = tblHistory.AutoNumber SET
tblHistory.CheckAmount = ([tblchecknumber].[amount1]), tblHistory.CheckNumber =
[tblchecknumber].[checknumber], tblHistory.CheckDate = [tblchecknumber].[checkdate],
tblHistory.AccountNumber = [tblchecknumber].[AccountNumber],
tblCheckNumber.Updated = "YES"

WHERE
(((tblHistory.CheckAmount) Is Null Or
(tblHistory.CheckAmount)=0) AND ((tblHistory.CheckNumber) Is Null) AND
((tblHistory.CheckDate) Is Null) AND
((tblHistory.AccountNumber) Is Null));

SQL conversion:
UPDATE
tblCheckNumber
SET tblHistory.CheckAmount = ([tblchecknumber].[amount1]), tblHistory.CheckNumber = [tblchecknumber].[checknumber], tblHistory.CheckDate = [tblchecknumber].[checkdate], tblHistory.AccountNumber = [tblchecknumber].[AccountNumber], tblCheckNumber.Updated = "YES"
FROM [DEV_TAXREF].[dbo].tblCheckNumber
INNER JOIN tblHistory
ON tblCheckNumber.Autonumber = tblHistory.AutoNumber
WHERE (((tblHistory.CheckAmount) Is Null Or (tblHistory.CheckAmount)=0) AND ((tblHistory.CheckNumber) Is Null) AND ((tblHistory.CheckDate) Is Null) AND ((tblHistory.AccountNumber) Is Null));

I get the following error:
The multi-part identifier "tblHistory.CheckAmount" could not be bound.

What is wrong?

View 3 Replies View Related

SELECT, JOIN And UPDATE

May 30, 2007

I need to Update a table with information from another table.  Below is my psuedo code - need help with the syntax needed for Sql2000 server.
JOIN tblStateLoc ON tblCompanies.LocationID = tblStateLoc.LocationIDUPDATE tblCompaniesSET tblCompanies.StoreType = tblStateLoc.StoreTypeWHERE tblCompanies.LocationID = tblStateLoc.LocationID

View 2 Replies View Related

Update Right Join Sql Problem

Jan 28, 2008

 Hi all,I have a problem with a sql update statement in a store procedure :update table1 set id_cl=t2.id_cl, mail=t2.mail from [table1]t1 right join [table2]t2 on t1.id_cl=t2.id_clWhat I would like is :- Update mail in table 1 from table2 when id_cl are identical -> works- Insert a new id_cl in table1 if it exists in table2 and not in table1 -> doesn't workI thought that the 'right join' would have been able to do that but apparently not.Could you help on this ?Thank you

View 4 Replies View Related

Should I Join The Update Queries

Mar 21, 2004

I have a page that will require several hundred update queries to be sent to the database. How much of a performance increase will i get by joining them all into one statement and sending them as a batch instead of running them one by one?

Thanks.

View 5 Replies View Related

Update Table With JOIN

Jun 2, 2004

Ok, I have tried this a million ways, and I am just stumped...

I have a access statement I am trying to convert to a T-SQL Statement.. There is no reason I should be having such a hard time with a simple query. Please Help!


UPDATE Tbl1
INNER JOIN Tbl2 ON Tbl1.ID = Tbl2.ID
SET tbl1.Field1 = tbl2.Field1

View 2 Replies View Related

Update A Field Using A W/Join. Please Help.

Nov 21, 2005

I am tryin t to update the tbl1_ID from the tbl2_ID. How do I do a Update Join that will do a comparison on the param column and value columns so that I could get the correct ID into tble 1.
Tbl1 is my destination table and tbl2 is my source. Please Help.



ID Tbl1_ID tb1Param tbl1value

1NULLParam1 0
1NULLParam2 F
1NULLParam3 2
3NULLParam1 0
3NULLParam2 E
3NULLParam3 0
5NULLParam1 0
5NULLParam2 F
5NULLParma3 2



tbl2_ID tbl2Param tbl2value

100param1 0
101param1 1
102param1 2
103param1 3
104param1 4
105param2 E
106param2 F
107param2 H
108param2 HF
109param2 HS
110param2 L
111param2 LS
112param3 0
113param3 1
114param3 2
115param3 3
116param3 4
117param3 5
118param3 6


Here is what Im trying to do if you can understand this.

Update Tbl1
SET tb1ID = B.tbl2_ID
FROM tbl1 AS A JOIN tbl2 AS B
ON A.tbl1Param + A.tbl1Value = B.tbl2Param + A.tbl2value

View 2 Replies View Related

UPDATE Using Aggregate And Inner Join

Aug 24, 2004

I want the sum of an amount to update another table using inner join.

The following query -

UPDATE A
SET A.[Col3] = SUM(B.[Col3])
FROM A
INNER JOIN B
ON A.C1 = B.C1
ANDA.C2= B.C2

Gives me an error saying I cannot use SET with aggregate Column.

How do I perform this aggregate update?

Thanks,

Vivek

View 1 Replies View Related

How To JOIN In UPDATE - Across 2 Tables

Oct 15, 2014

I have a customers CRM DB that I need to run an update query on, affecting around 14,000 records. The fields used in the entity in question are split across two tables. I need to update a field in one tabled based off of the result of a field in the other table.

So this is what I started with:

USE db1
UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';

This didn't work, SQL complained about being unable to bind tbl1.CreatedOn. I assume because it's in a different table to the one I'm updating.

I attempted a JOIN to the best of my limited SQL knowledge, thinking I could just shove the two tables together and it might be happy.

USE db1
INNER JOIN tbl1 ON tbl2.Id=tbl1.Id;
UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';

This also didn't work, complaining of syntax error near 'INNER'

I'm obviously missing something, but IU don't know what it is.

View 5 Replies View Related

Update One Table Using Join With Another?

Mar 13, 2006

I have a table with date values that are currently null.

I've created a query that identifies the dates that should go into the date field for the table. I'll call the table "shipping" and the SQL Query, "query".


query has the following fields:
CN int
CSN int
shipdate datetime

shipping has the following fields that are relavent:
CN int
CSN int
shipped_date datetime

I want to update shipping, setting shipping.shipped_date=query.shipdate
where query.cn=shipping.cn and query.csn=shipping.csn

How do I word it to get a proper update using query analyzer?

View 3 Replies View Related

Concatenate On Update Join

Jul 23, 2007

Looking to concat. two fields (w/o having to go to external scripting) in the process of an update that's running through a join.

Currently (to get events that cross today and update from the 'event' table to the current/daily table):
UPDATE tblEmployee SET tblEmployee.Status = tblFuture.Status
FROM tblFuture INNER JOIN tblEmployee
ON tblFuture.EmpID = tblEmployee.EmpID
WHERE DateDiff(DAY,GETDATE(),tblFuture.Start_Date) <= 0 AND DateDiff(DAY,GETDATE(),tblFuture.End_Date) >= 0

But I want to be able to set:
tblEmployee.Status = tblFuture.Status + tblFuture.Remarks ...

That doesn't work.

I've searched a bit here and there, but nothing successful thus far. Kind of new to SQL Server (not that I've done this in Oracle or MySQL). Anyhoo ... any ideas?

TIA!

View 6 Replies View Related

Help With Inner Join In Update Statement

Aug 27, 2007

Here is my update statements which doesn't work, can you show me an example or provide a hint.

thanks

update property
inner join propinv on propinvid=propertyinvid
set property.lotsize='100'
where property.lotsize <> '' and property.lotize is not null

Thank you

View 9 Replies View Related







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