Update Statement Using Nested Query

update statement using nested query

Something like this?

UPDATE mytable SET lastLogDate = t.maxDateForUser  
FROM  
(  
    SELECT userid, MAX(logDate) as maxDateForUser  
    FROM mytable  
    GROUP BY userId  
) t  
WHERE mytable.userid = t.userid

UPDATE a table using a nested SELECT statement in the WHERE clause?

If you want to use a strategy to perform an updates using a subquery, please try this (please also note the change from '=' to 'in'):

UPDATE
 t1
SET
 t1.value1 = '0'
From 
 TABLE1 t1
WHERE
 t1.EMPLOYID in (SELECT t2.Employid FROM TABLE1 t2 WHERE t2.FIELD1 = 'EXPN' and t2.value1='1')
and
 t1.FIELD1 = 'EXPN'

However and as other have noted, you don't need a subquery per your question. This should work just fine, and still give you flexibility to add more tables as needed:

UPDATE
 t1
SET
 t1.value1 = '0'
From 
 TABLE1 t1
WHERE
 t1.FIELD1 = 'EXPN'
and
 t1.value1='1'

Update query using Subquery in Sql Server

you can join both tables even on UPDATE statements,

UPDATE  a
SET     a.marks = b.marks
FROM    tempDataView a
        INNER JOIN tempData b
            ON a.Name = b.Name

• SQLFiddle Demo

for faster performance, define an INDEX on column marks on both tables.

using SUBQUERY

UPDATE  tempDataView 
SET     marks = 
        (
          SELECT marks 
          FROM tempData b 
          WHERE tempDataView.Name = b.Name
        )

• SQLFiddle Demo

MYSQL update with nested subquery

You can use the following query to get the UPDATE:

UPDATE Child SET Child.grandparent_id = (
    SELECT GrandParent.id 
    FROM GrandParent INNER JOIN Parent ON GrandParent.id = Parent.grandparent_id
    WHERE Parent.id = parent_id
) WHERE Child.grandparent_id IS NULL;

Demo: http://sqlfiddle.com/#!9/894e97/1/0 (modified table content to show the UPDATE is working).

Hint: Your "correct" example is wrong: GrandParent of Parent with id = 2 is 1!

How to update rows using nested queries

You don't need all those selects in sub queries an update is a row by row update.

drop table if exists t;
create table t
(IC_NO  varchar(20),       D_BIRTH  date, country varchar(20));
insert into t values
(940525053455 , '0000-00-00','malasia'),
(960525053455 , '1995-05-25','malasia'),
(940525053455 , '0000-00-00','aa');

update t
        set d_birth = str_to_date(concat('19',SUBSTR(`IC_NO`, 1, 2),SUBSTR(`IC_NO`, 3, 2) ,SUBSTR(`IC_NO`, 5, 2)),'%Y%m%d')
where country = 'malasia' and d_birth = '0000-00-00'
;

select * from t;

+--------------+------------+---------+
| IC_NO        | D_BIRTH    | country |
+--------------+------------+---------+
| 940525053455 | 1994-05-25 | malasia |
| 960525053455 | 1995-05-25 | malasia |
| 940525053455 | 0000-00-00 | aa      |
+--------------+------------+---------+
3 rows in set (0.00 sec)

how to update using nested query in SQL

Give this a try

Update t
Set t.yyyy = q.Name
From TableToUpdate t
Join AddressTable q on q.Address = t.Address

This assumes that Address field (which you are joining on) is a one to one relationship with the Address field in the table you are updating

This can also be written

Update TableToUpdate
Set yyyy = q.Name
From AddressTable q
WHERE q.Address = TableToUpdate.Address

Since the update table is accessible in the FROM/WHERE clauses, except it cannot be aliased.

Update query teradata with nested select statement

If you want to do an UPDATE you need to use SET to set the values. Try something like:

UPDATE DL_RG_ANALYTICS.SH_historico_1 
FROM (
  SELECT 
    ANO_MES, TIP_TAR,DIA_PAGO, MARCA_RENEG, MONTO, TRAMO_MORA, DIA_PAGO, CASOS, CANAL,
    TRAMO_PAGO, TIPO_MORA, MES,CAST((MAX_DIA - DIA_PAGO) AS INTEGER) AS DIAS_AL_CIERRE_1
  FROM (
    SELECT * 
    FROM DL_RG_ANALYTICS.SH_historico_1 A
    LEFT JOIN (
      SELECT ANO||MES AS ANOMES, MAX(DIA) AS MAX_DIA 
      FROM DL_RG_ANALYTICS.SH_CALENDARIO
      GROUP BY 1
    ) B ON A.ANO_MES = B.ANOMES
  ) M
) src -- source rows
SET col1 = src.col1 -- set new value(s)
WHERE SH_historico_1.pk_col = src.pk_col  -- needs to be 1-1

Just make sure your join condition in the WHERE clause is a 1-1 relationship so you don't have multiple source rows trying to update a single target row.

update using nested queries in oracle sql-developer

You need to use MERGE statement for this purpose since Oracle does not support UPDATE statement with table as a source. But as long as your new value does not depend on any calculated value, this can be done with simple IN predicate:

update nltl t
set is_locked = 0
where (l_id, c_id, e_id, t_id, ee_id, locked_dt) in (
  select ppl.l_id, ppl.c_id, ppl.e_id, ppl.t_id, ppl.ee_id, ppl.locked_dt
  from (
    select
      ppl.*,
      dense_rank() over (order by ppl.locked_dt desc) my_rnk
    from nltl ppl
    where
      ppl.l_d = 17
      and ppl.t_id = 55
      and ppl.c_id = 3
      and ppl.e_id = 1509919001
      and ppl.ee_id = 15099190
  )
  where my_rnk = 3
)

Or use physical row addres to do it without indexes or full table scan:

update nltl t
set is_locked = 0
where rowid in (
  select rid
  from (
    select
      ppl.rowid as rid,
      dense_rank() over (order by ppl.locked_dt desc) my_rnk
    from nltl ppl
    where
      ppl.l_d = 17
      and ppl.t_id = 55
      and ppl.c_id = 3
      and ppl.e_id = 1509919001
      and ppl.ee_id = 15099190
  )
  where my_rnk = 3
)

MySQL update from nested SELECT query

Try this:

UPDATE products p
LEFT JOIN stock s ON p.code = s.code
SET p.stock = 0
WHERE p.stock > 0 AND p.product_active = 1 AND s.code IS NULL

---

Related Topics