UPDATE and SELECT to/from the same table

hoggar Published at Dev

hoggar

Table1:

BrID    HQID
------------
Br1     HQ1
HQ1     
HQ2     
Br2     HQ1
Br3     HQ2

Table2:

ID    Name        BrID    IDt2
------------------------------
11    OthName11   Br1     
22    HQName111   HQ1
33    HQName222   HQ2
44    OthName22   Br2     
55    OthName33   Br3

I need to UPDATE Table2.IDt2 for each row from Table1 which has HQID value but with Table2.ID connected by Table1.HQID = Table2.BrID

In other words fro the first row .

SELECT HQID FROM Table1 WHERE BrID = 'Br1'
SELECT ID WHERE Table2.BrID = HQID (from previous SELECT)
UPDATE Table2 SET IDt2 = ID(from previous SELECT) WHERE Table2.BrID = 'Br1' (from first SELECT)

This should be the result Table2:

ID    Name        BrID    IDt2
------------------------------
11    OthName11   Br1     22
22    HQName111   HQ1
33    HQName222   HQ2
44    OthName22   Br2     22
55    OthName33   Br3     33

Is it possible to make it in one UPDATE?
How else can I do this?

Hart CO

You just need multiple JOIN statements:

UPDATE a
SET a.IDT2 = c.ID
FROM Table2 a
JOIN Table1 b
  ON a.BRID = b.BRID
JOIN TAble2 c 
  ON b.HQID = c.BRID

Demo: SQL Fiddle

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-11

Comments

0 comments

From Dev

Update row with select on same table

From Dev

mysql update with select same table

From Dev

update and select query together to update table data from same table

From Dev

MySQL select and update table at same time

From Dev

Update a column using a select subquery to the same table

From Dev

MySQL UPDATE from same table and SELECT

From Dev

UPDATE and SELECT from same table in one query

From Dev

MySQL update a table and select from the same table in a subquery

From Dev

MySQL select and update multiple rows from same table

From Dev

How to use update and select form the same table ? mysql

From Dev

Select and update same table and row in one connection in mysql

From Dev

SELECT and then update in the same column

From Dev

Update same tables from from select value from same table mysql

From Dev

MySQL select in same table

From Dev

Multiple SELECT on the same table

From Dev

Using SELECT with UPDATE in the same query

From Dev

UPDATE and SELECT a row in the same transaction

From Dev

Oracle: Select and Update at the same time

From Dev

MySQL Update with same table subquery

From Dev

UPDATE based on DISTINCT in same table

From Dev

Update a table with the value of the same row

From Dev

Trigger to update same table data

From Dev

Mysql update table at the same time

From Dev

MySQL Update with same table subquery

From Dev

UPDATE based on DISTINCT in same table

From Dev

subquery mysql on Update in same table

From Dev

Opening Same Table Twice In Update

From Dev

Update multiple records in same table

From Dev

Update with SubQuery from same table

Related Related

Article