SQL Statement with multiple SETs and WHEREs
NO!
You'll need to handle those individually
Update [table]
Set ID = 111111259
WHERE ID = 2555
Update [table]
Set ID = 111111261
WHERE ID = 2724
--...
SQL multi SET with one WHERE
This is possible,
UPDATE table SET Name = 'aaa', Col2 = 'bbb' WHERE ID IN (2555, 2666)
What you are asking for is setting multiple values to same column and same row.
SQL Statement using Where clause with multiple values
Try this:
select songName from t
where personName in ('Ryan', 'Holly')
group by songName
having count(distinct personName) = 2
The number in the having should match the amount of people. If you also need the Status to be Complete
use this where
clause instead of the previous one:
where personName in ('Ryan', 'Holly') and status = 'Complete'
Multiple set and where clauses in Update query in mysql
I decided to use multiple queries all in one go. so the code would go like
UPDATE table SET col2='value1' WHERE col1='setting1';
UPDATE table SET col2='value2' WHERE col1='setting1';
etc
etc
I've just done a test where I insert 1500 records into the database. Do it without starting a DB transaction and it took 35 seconds, blanked the database and did it again but starting a transaction first, then once the 1500th record inserted finish the transaction and the time it took was 1 second, so definetely seems like doing it in a db transaction is the way to go.
SQL - Update multiple records in one query
Try either multi-table update syntax
UPDATE config t1 JOIN config t2
ON t1.config_name = 'name1' AND t2.config_name = 'name2'
SET t1.config_value = 'value',
t2.config_value = 'value2';
Here is a SQLFiddle demo
or conditional update
UPDATE config
SET config_value = CASE config_name
WHEN 'name1' THEN 'value'
WHEN 'name2' THEN 'value2'
ELSE config_value
END
WHERE config_name IN('name1', 'name2');
Here is a SQLFiddle demo
sql query with multiple where statements
You need to consider that GROUP BY
happens after the WHERE
clause conditions have been evaluated. And the WHERE
clause always considers only one row, meaning that in your query, the meta_key
conditions will always prevent any records from being selected, since one column cannot have multiple values for one row.
And what about the redundant meta_value checks? If a value is allowed to be both smaller and greater than a given value, then its actual value doesn't matter at all - the check can be omitted.
According to one of your comments you want to check for places less than a certain distance from a given location. To get correct distances, you'd actually have to use some kind of proper distance function (see e.g. this question for details). But this SQL should give you an idea how to start:
SELECT items.* FROM items i, meta_data m1, meta_data m2
WHERE i.item_id = m1.item_id and i.item_id = m2.item_id
AND m1.meta_key = 'lat' AND m1.meta_value >= 55 AND m1.meta_value <= 65
AND m2.meta_key = 'lng' AND m2.meta_value >= 20 AND m2.meta_value <= 30
Multiple WHERE conditions in one SQL statement
You cannot have multiple WHERE
at the same level of SQL query. You need to use AND
:
SELECT * FROM Table
WHERE ID = ?
AND COL2 = ?
AND COL3 = ?
-- ...
AND COL8 = ?
or use nesting:
SELECT *
FROM (SELECT *
FROM Table
WHERE ID = ?) s
WHERE COL1 = ?
...
It is actually an interesting question. For instance KQL(Kusto query language) allows to chain multiple WHERE
:
Tab
| where col = ?
| where col2 = ?
Sample:
let t1 = datatable(key:long, value:string)
[1, "a",
2, "b",
3, "c"];
t1
| where key in (1,2)
| where value == "b"
Update multiple rows with multiple 'where' clauses for each individual row
give this a try by using CASE
Update MyTable
SET value = CASE
WHEN game_id = 1 AND x = -4 AND y = 8 THEN 1
WHEN game_id = 1 AND x = -3 AND y = 7 THEN 2
WHEN game_id = 2 AND x = 5 AND y = 2 THEN 3
ELSE value
END
WHERE game_ID IN (1,2,3) AND -- the purpose of this WHERE clause
x IN (-4, -3, 5) AND -- is to optimize the query by preventing from
y IN (8,7,2) -- performing full table scan.
SQL: Update table where column = Multiple Values
Replace ProductID =
with ProductID IN
Update Products
Set ProductName = 'Shoes'
WHERE ProductID IN (1,2,3,4,5,6,7,8)
Related Topics
Split Varchar into Separate Columns in Oracle
How to Rename an Index in MySQL
How to Store a String Var Greater Than Varchar(Max)
How to Perform a Bitwise Group Function
Adding a Uniqueidentifier Column and Adding the Default to Generate New Guid
SQL 2005 Cte VS Temp Table Performance When Used in Joins of Other Tables
SQL Server - Does [Select] Lock [Update]
Update Values in Identity Column
How to Insert Data into Table Using Stored Procedures in Postgresql
Will a SQL Server Job Skip a Scheduled Run If It Is Already Running
Sql: How to Find Duplicates Based on Two Fields
How to Schedule a Stored Procedure
How to Use Non-Aggregate Columns with Group By