How Is This Script Updating Table When Using Left Joins

UPDATE multiple tables in MySQL using LEFT JOIN

UPDATE  t1
LEFT JOIN
        t2
ON      t2.id = t1.id
SET     t1.col1 = newvalue
WHERE   t2.id IS NULL

Note that for a SELECT it would be more efficient to use NOT IN / NOT EXISTS syntax:

SELECT  t1.*
FROM    t1
WHERE   t1.id NOT IN
        (
        SELECT  id
        FROM    t2
        )

See the article in my blog for performance details:

• Finding incomplete orders: performance of LEFT JOIN compared to NOT IN

Unfortunately, MySQL does not allow using the target table in a subquery in an UPDATE statement, that's why you'll need to stick to less efficient LEFT JOIN syntax.

Redshift: UPDATE statement using a LEFT JOIN returns table name specified more than once error

Try this. (it's working)

with temp AS
(
  SELECT A.* FROM tbl_A A
  LEFT JOIN tbl_B B
  ON A.Mathcing_Column_Name = B.Matching_Column_Name
  WHERE B.Matching_Column_Name is NULL
)
UPDATE tbl_A C  SET column_Name = 'Y' 
from temp D
where C.id=D.id 

Update a table using JOIN in SQL Server?

You don't quite have SQL Server's proprietary UPDATE FROM syntax down. Also not sure why you needed to join on the CommonField and also filter on it afterward. Try this:

UPDATE t1
  SET t1.CalculatedColumn = t2.[Calculated Column]
  FROM dbo.Table1 AS t1
  INNER JOIN dbo.Table2 AS t2
  ON t1.CommonField = t2.[Common Field]
  WHERE t1.BatchNo = '110';

If you're doing something silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) - see here and here for more details:

;WITH t2 AS
(
  SELECT [key], CalculatedColumn = SUM(some_column)
    FROM dbo.table2
    GROUP BY [key]
)
UPDATE t1
  SET t1.CalculatedColumn = t2.CalculatedColumn
  FROM dbo.table1 AS t1
  INNER JOIN t2
  ON t1.[key] = t2.[key];

The reason this is silly, is that you're going to have to re-run this entire update every single time any row in table2 changes. A SUM is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.

Updating from a select query with a left join

What you heard is correct. You cannot SELECT and UPDATE in one statement. Your PHP code has to execute two different SQL commands. After selecting, use PHP with your result to figure out the IDs of the messages you're returning, and then execute an UPDATE statement on the messages table, something like "UPDATE messages SET mRead=1 WHERE msgId IN (?)".

SQL Server - Using joins in Update statement

You could use EXISTS and a correlated subquery to filter on rows whose UserId has at least one audit event of id 14:

UPDATE h
SET IsActivated = 1
FROM [dbo].HRUser h
WHERE EXISTS (
    SELECT 1 FROM 
    FROM dbo.[UserAudit] a
    WHERE a.UserId = h.UserId AND a.AuditTypeId = 14
)

Note that there is no point reopening the target table in the subquery; you just need to correlate it with the outer query.

using an inner or left join in an update?

Left Join

Select *
from A 
left join B on a.id = b.pid

In the conditions a.id = b.pid, every row is returned for both A and B BUT if the value from A (i.e. a.id) doesn't match the value from B (i.e., B), all fields of B will be null. On the other hand, for those rows where this condition is true, all values for B are shown. Notice that A's values have to be returned because it is on the left of the 'left' keyword.

Inner Join

Select *
from A 
inner join B on a.id = b.pid

Rows are returned for rows where a.id = b.pid is true, otherwise no rows are returned if it is false. This is a mutually exclusive join.

In your case, don't use a left join because all records on the left of the 'left' keyword will be updated with null or non-null values. This means you will unintentionally update non-matched record with null values based upon my description of left joins.

---

Related Topics