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 ARTICLENUMBER
s 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
Exclude a Column Using Select * [Except Columna] from Tablea
How to Delete a MySQL Record After a Certain Time
How to Merge Multiple Rows into Single in Oracle
Retrieve Varbinary Value as Base64 in Mssql
Sql Server Query to Find All Permissions/Access for All Users in a Database
How to Merge Rows on Specific Condition
Use Current Date as Default Value for a Column
How to Change Date Format in .Net Rdlc Report
How to Subtract One Day from Current Date Then Convert to String in Hive
How to Check If More Than One Column Is Not Null Per Record
Best Way to Iterate Through Columns in a SQL Table
How to Check If a SQL Server String Is Null or Empty
How to Execute a Stored Procedure Once for Each Row Returned by Query
Sql Server: Check If Variable Is Null and Then Assign Statement for Where Clause
Select Ids from Multiple Rows Where Column Values Satisfy One Condition But Not Another