Update Multiple Rows in a Table from Another Table When Condition Exists

Update multiple rows in a table from another table when condition exists

Assuming that by

insert "the_geom" lat/lng values

you actually mean to UPDATE existing rows in table2:

UPDATE table2 t2
SET the_geom = t1.the_geom
FROM table1 t1
WHERE t2.address = t1.address
AND t2.the_geom IS DISTINCT FROM t1.the_geom; -- avoid empty updates

Related answer:

  • How do I (or can I) SELECT DISTINCT on multiple columns?

Also assuming that the address column has UNIQUE values.

Details for UPDATE in the manual.

Update multiple rows based on other table column values

Try this:

UPDATE wrkque
SET locacc = (SELECT locmst.locacc
FROM locmst
WHERE locmst.stoloc = wrkque.srcloc)
WHERE wrkque.oprcod = 'TRN'

The inner query needs to be referenced to the outer query, which we're doing here: WHERE locmst.stoloc = wrkque.srcloc. We don't need the join inside the inner query, we simply need the reference.

Edit for EXISTS:

As Gordon points out in his answer, you should include an EXISTS correlation clause in the outer query as well:

UPDATE wrkque
SET locacc = (SELECT locmst.locacc
FROM locmst
WHERE locmst.stoloc = wrkque.srcloc)
WHERE wrkque.oprcod = 'TRN'
AND EXISTS (SELECT locmst.stoloc
FROM locmst
WHERE locmst.stoloc = wrkque.srcloc)

Without this, any rows that do not meet locmst.stoloc = wrkque.srcloc (exist in wrkque but not in locmst) will have locacc set to null. If every row exists in both tables, you can leave it out, but it's better practice to always include it.

Update multiple rows in table based on conditions from another table

For mysql

UPDATE a 
JOIN b ON a.id = b.empid
SET a.name = NUll
WHERE b.datewrk <= '2012-12-14';

You don't need a subquery just join your table put set clause in right place then where clause

Fiddle Demo

Update row in a table based on multiple rows in another table

I would recommend EXISTS:

UPDATE table1 t1
SET t1.integer = (EXISTS (SELECT 1
FROM table2 t2
WHERE t2.id = t.id AND
t2.boolean
)
);

This can take advantage of an index on table2(id, boolean). With such an index, it should be faster than an approach that uses JOIN and AGGREGATION.

Update a table with data from other table with multiple conditions?

I suspect that you really want EXISTS -- that is to set all values in table A, with 1 if there is a non-NULL matching event. That would be:

UPDATE A
SET NEWCOLUMN = (CASE WHEN EXISTS (SELECT 1
FROM B
WHERE b.ARTICLENUMBER = a.ARTICLENUMBER AND
b.EVENT IS NOT NULL
)
THEN 1 ELSE 0
END);

Note that this updates all rows in A -- even those with no matching article in B. As I say, I think this is what you want to do, although it is not exactly how your question is phrased. Your question does not specify what to do for ARTICLENUMBERs that are not in B.

MYSQL - UPDATE multiple rows from another table

You have to set the values in your update query to get the changes.

Example:

update tableA inner join tableB on tableA.id=TableB.id
set tableA.col1=TableB.col1,
tableA.col2=TableB.col2,
tableA.col3=TableB.col3;

and also you can add more conditions in where clause to make query run on filtered records.

Update multiple columns from a specific row in another table

You can use apply:

update r
set r.robotname = rat.robotname,
r.robotcolor = rat.robotcolor
from robot r cross apply
(select top (1) rat.*
from robotaudittable rat
where rat.robotnumber = r.robotnumber
order by rat.updateddate asc
) rat;


Related Topics



Leave a reply



Submit