Update and select in one query
UPDATE tblPopUp
SET PopUp = 'False', Period = Period
OUTPUT DELETED.Period
WHERE DisplayNo = 1
For more information about OUTPUT clause please check this post.
How do I UPDATE from a SELECT in SQL Server?
UPDATE
Table_A
SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
FROM
Some_Table AS Table_A
INNER JOIN Other_Table AS Table_B
ON Table_A.id = Table_B.id
WHERE
Table_A.col3 = 'cool'
Is there a way to SELECT and UPDATE rows at the same time?
Consider looking at the OUTPUT clause:
USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT inserted.BusinessEntityID,
deleted.VacationHours,
inserted.VacationHours,
inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
Mysql: First Select then Update in One Query
The operation you describe, returning a resultset from a SELECT
and performing an UPDATE
cannot be performed in a single SQL statement in MySQL.
You would need two separate SQL statements: a SELECT
and an UPDATE
.
In terms of MySQL, it is not necessary to run a SELECT
before running an UPDATE
, it's possible to run just an UPDATE
.
UPDATE `messages` SET somecol = 'somevalue' WHERE `timestamp`>'1422680952'
(The query in the question is enclosed in double quotes. That leads us to suspect that you are running this statement from a database interface library in a language such as PHP.)
It is possible to get multiple statements to execute as part of a single transaction. But as far as how the MySQL server itself is actually processing the specified operations, that's going to be two separate statements.
How to select and update in one query?
Resolve this!
DECLARE @id int;
SET @id = (select top(1) id from [table] where [x] = 0 order by id desc);
select * from [table] where id = @id;
update [table] set [x] = 20 where id = @id;
:D
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
MySQL - UPDATE query based on SELECT Query
You can actually do this one of two ways:
MySQL update join syntax:
UPDATE tableA a
INNER JOIN tableB b ON a.name_a = b.name_b
SET validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here
ANSI SQL syntax:
UPDATE tableA SET validation_check =
(SELECT if(start_DTS > end_DTS, 'VALID', '') AS validation_check
FROM tableA
INNER JOIN tableB ON name_A = name_B
WHERE id_A = tableA.id_A)
Pick whichever one seems most natural to you.
SELECT, UPDATE, DELETE with one SQL query?
Short answer is that it's not possible without wrapping the extra queries inside a trigger or procedure.
You can do this in a transaction, and without a SELECT
, but it will take 3 queries:
START TRANSACTION;
UPDATE cart_items
SET quantity = quantity - 1
WHERE cart_id = {$cart_id}
AND id = {$cart_item_id};
DELETE
FROM cart_items
WHERE quantity = 0
AND cart_id = {$cart_id}
AND id = {$cart_item_id};
DELETE c
FROM cart c
LEFT JOIN cart_items ci
ON ci.cart_id = c.id
WHERE c.id = {$cart_id}
AND ci.cart_id IS NULL;
COMMIT;
The last DELETE
joins cart to cart_items, and deletes the cart if none are found (cart_items fields are NULL
).
I have included available identifiers to speed up the DELETE
s, although they should be fine without them.. it'll just look for and pick up any other quantity 0 items or empty carts.
Related Topics
How to Merge the Columns from Two Tables into One Output
Using Group by and Having Clause
In General, Should Every Table in a Database Have an Identity Field to Use as a Pk
From a Sybase Database, How to Get Table Description ( Field Names and Types)
Bulk Insert with Variable File Name
Creating a Composite Foreign Key in SQL Server 2008
How to Find the Number of Occurrences of a Particular Character in a String Using SQL
How to Use a SQL Update Statement to Add 1 Year to a Datetime Column
Comma-Separated List as a Result of Select Statement in Oracle
How to Get a Value from Previous Result Row of a Select Statement
Querying More Than 65536 Rows Error in Excel 2013
How to Create Birt Report Based on Multiple Data Sets
How to Specify in Clause in a Dynamic Query Using a Variable