Update with Self-Join

Update with self-join

Oracle does not support JOIN clause in UPDATE statements.

Use this:

MERGE
INTO    contactassociations ca1
USING   contactassociations ca2
ON      (
        ca1.contactid = ca2.contactid
        AND ca1.entitytable = 'EMPLOYER'
        AND  ca2.entitytable = 'CLIENT'
        )
WHEN MATCHED THEN
UPDATE
SET     parentid = ca2.id

Update using self join

This will update the referenced course_id of corresponding lab_code.

UPDATE a
FROM uet_tmp_course a
INNER JOIN uet_tmp_course b
ON a.LAB_CODE = b.CODE
SET a.LAB_CODE = b.course_id;

For Oracle

UPDATE 
(SELECT t1.LAB_CODE as OLD, t2.course_id as NEW
 FROM uet_tmp_course t1
 INNER JOIN uet_tmp_course t2
 ON t1.LAB_CODE = t2.CODE
) t
SET t.OLD = t.NEW

And there is something else you could do to prevent updating rows every time (remeber to manage this in your app too):

alter table uet_tmp_course
add constraint FK_uet_tmp_course_lab
foreign key (lab_code) references uet_tmp_course(course_id)

update statement using self join condition

A suggestion, before executing an update with inner join try the same query using select statement to view the rows returned.
In your case you can't use update with join because the condition supervisor_id = 114 will remove the value of supervisor_id=100 .

You should use subquery ,but in update or delete you cant specify the same table , so you should wrap the subquery into an outer query giving the table an alias.

This nested query is not very good for performance.

Try:

update employee set supervisor_id= ( select t.supervisor_id 
                                     from ( SELECT supervisor_id 
                                            FROM   employee 
                                            WHERE  employee_id = 114
                                             ) as t 
                                    )
where  supervisor_id=114; 

Demo: https://www.db-fiddle.com/f/qff694udysNgqbyJyFcDzn/8

Result:

How to UPDATE a column from a self join table in PostgreSQL?

You were using the Microsoft-Syntax for UPDATE.
(and you don't need the COALESCE(), since a.propertyaddress is always NULL)

Postgres documentation for UPDATE

---

UPDATE Housing a
SET propertyaddress = b.propertyaddress
FROM Housing b
WHERE a.ParcelID = b.ParcelID
  AND a.uniqueid <> b.uniqueid
  AND a.propertyaddress IS NULL
  ; 

Oracle - Self Join to update a column

Use a subquery:

UPDATE CUST_VW C
    SET CUST_ID = (SELECT MAX(C2.CUST_ID)
                   FROM CUST_VW C2
                   WHERE C.REQUEST_NUM = C2.REQUEST_NUM AND
                          C2.CUST_ID IS NOT NULL
                  )
    WHERE CUST_ID IS NULL ;

The _VW strongly suggests that you have a view, which may not be updatable. You should really be updating the underlying tables and not the view.

Self join to update table in SQL

You could use an Update statement that uses a subquery or CTE, but I don't think in reality your table could be so simplistic without any Id (for example an id that would link all those Part* to a particular Product. Anyway, as is:

Update myTable
   set Qty = agg.Qty, Orders=agg.Orders 
from (Select Sum(Qty) Qty, Sum(Orders) Orders from myTable where level > 0) agg
where level=0;

DBFiddle demo

Update values in a table based on a self-join query to itself in Oracle

I hope the solution you are looking for can be served up using MERGE statement. I hope the query which you have posted is correct. I have build the solution on top of the query. Let me know if this helps.

MERGE INTO ItemLog it USING
(SELECT ItemID, MessageTime, 
    CASE WHEN A.Voltage<B.Voltage and A.Voltage<C.Avg_Voltage and C.SD_Voltage<5 THEN 'Good' ELSE 'Bad' END Calculated_Status1, 
    CASE WHEN A.Temperature<B.Temperature and A.Temperature>C.Temperature and C.SD_Temperature>10 THEN 'Good' ELSE 'Bad' END Calculated_Status2 
FROM ItemLog A,
   (SELECT F.ItemID,
        F.MessageTime Key_MessageTime,
        S.Voltage,
        S.Temperature
    FROM ItemLog F,
        ItemLog S
    WHERE F.ItemID=S.ItemID 
        and S.MessageTime=
            SELECT MAX(MessageTime)
            FROM ItemLog
            WHERE ItemID=F.ItemID
               and MessageTime<F.MessageTime
               and Voltage<12
               and Temperature<125
    ) B,  -- Returns the Voltage and Temperature from the prior time it was <12 and <125
   (SELECT K.ItemID, K.MessageTime,
        AVG(L.Temp) Avg_Temperature, STDDEV(L.Temperature) SD_Temp, 
        AVG(L.Voltage) Avg_Voltage, STDDEV(L.Voltage) SD_Voltage 
    FROM ItemLog K,
        ItemLog L
    WHERE K.ItemID=L.ItemID 
        and L.MessageTime=
            SELECT MAX(MessageTime)
            FROM ItemLog
            WHERE ItemID=K.ItemID
               and MessageTime<K.MessageTime
    GROUP BY K.ItemID, K.MessageTime
     ) C  -- Returns the Voltage and Temperature stats from all prior messages
   (SELECT ItemID 
    FROM ItemLog
    WHERE Voltage>40
     ) D  -- Returns all ItemID where Voltage was ever >40, to exclude them
WHERE A.ItemID=B.ItemID and A.MessageTime=B.MessageTime
  and A.ItemID=C.ItemID and A.MessageTime=C.MessageTime
  and A.ItemID=D.ItemID(+) and D.ItemID IS NULL)z
ON
(it.ItemID = z.ItemID AND it.MessageTime = z.MessageTime)
WHEN MATCHED THEN
UPDATE SET it.STATUS1 = z.Calculated_Status1,
       it.STATUS2 = z.Calculated_Status2;

Update based on conditions in SQL Server using self join

You had Where missing at the end.

Try this:

UPDATE child SET
IsIntTr =   
   CASE WHEN child.InvID = parent.InvID THEN 1
        ELSE NULL
   END,

IsTr     =  
   CASE WHEN child.InvID = parent.InvID THEN 0
        ELSE 1
   END
from    MyTable child (NOLOCK),     
        MyTable parent (NOLOCK)
WHERE
  child.ParentId = Parent.Id

or in SqlFiddle.

SELF JOIN to Update Rows Of the Table

The following query will give you the expected results

(Test code https://gist.github.com/1969171 for those that want to play)

PID Date       M  Y    Generation  Expected CarryOver   
10  2011-10-01 10 2011 56.748      56       0.748
10  2011-11-01 11 2011 12.004      12       0.752
10  2011-12-01 12 2011 10.632      11       0.384
10  2012-01-01 01 2012 11.928      12       0.312
10  2012-02-01 02 2012 7.58        7        0.892
100 2011-12-01 12 2011 5.897       5        0.897
100 2012-01-01 01 2012 0.881       1        0.778

---

DECLARE rowItems CURSOR FOR 
SELECT ProjectId, [Month], [Year], Generation FROM TTable
ORDER BY ProjectId,[Year] ,CAST([Month] as int)

DECLARE @p int, @m VARCHAR(5), @y int, @g FLOAT, @priorP int, 
        @carryOver FLOAT, @expected FLOAT

OPEN rowItems

FETCH NEXT FROM rowItems INTO @p, @m, @y, @g

SET @priorP = -1
SET @carryOver = 0.0

WHILE @@FETCH_STATUS = 0
BEGIN

   IF NOT @p = @priorP SET @carryOver = 0.0

   SET @expected = @g+@carryOver
   SET @carryOver = ROUND(@expected-FLOOR(@expected),3,0)   

   UPDATE TTable
     SET EXPECTED = FLOOR(@expected), CarryOver = @carryOver
   WHERE  ProjectId = @p and [Month] = @m and  [Year] = @y

   SET @priorP = @p

   FETCH NEXT FROM rowItems INTO @p, @m, @y, @g

END

CLOSE rowItems
DEALLOCATE rowItems

SELECT * FROM TTable

You need to do this in a loop. The query you use is getting the original value of the carry over column not the updated value.

---

Related Topics