Thesql update based on select result, How combine

MySQLselectupdate

Here are two SQL statements. One is an select statement and the other is a update statement. I want to combine them.

Select

SELECT * FROM test WHERE `id`='1'

Update

UPDATE test SET `select_count`=`select_count`+'1' WHERE `id`='1'

Best Answer

Based on your question I think you need to include SELECT statement in UPDATE statement:

Sample SQL in below:

create table test1
(ID int,
select_count int,
select_name varchar(50))

Insert into test1 values (1,1,'A')
Insert into test1 values (2,2,'B')

UPDATE test1
SET select_count = select_count + 1 
where select_count = (SELECT select_count FROM test1 WHERE select_name = 'A')

Another way: If you have 2 tables (TableA and TableB)

UPDATE TableB 
SET TableB.value = (
    SELECT TableA.value 
    FROM TableA
    WHERE TableA.name = TableB.name
)
where TableB.value < X;

Related Solutions

MySQL Select Statement – How to Make a Select Statement Get Blocked

Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them

You can have SELECTs get deadlocked by UPDATEs and DELETEs
- Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
You can have UPDATEs and DELETEs blocked by SELECTs
- How are DB locks tied to connections and sessions?
- Is Oracle DB immune to the InnoDB deadlocks found in MySQL?
You can just lock the table before the SELECT and unlock it afterwards
- LOCK TABLES table READ;
- SELECT ... ;
- UNLOCK TABLES;
You could perform a large UPDATE in a transaction with the SERIALIZEABLE isoaltion level. The MySQL Documentation on SERIALIZEABLE isoaltion level says:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Cutting right to the chase, you could just use

SELECT ... FOR UPDATE, then try SELECT against those rows
SELECT ... LOCK IN SHARE MODE, then try to UPDATE or DELETE those rows
See the MySQL Documentation

Give it a Try !!!

Mysql – Select and update specific thesql data in single query

You can make a SELECT as part of an UPDATE statement, but you can't make an UPDATE part of a SELECT statement. However, based on your example you don't need to, instead you can just do the following:

UPDATE parts
SET keywords = CONCAT(keywords,'Phillips')
WHERE name LIKE '%Phi%'

If you want to retrieve the results after you've updated them just execute the following afterwards:

SELECT *
FROM parts
WHERE name LIKE '%Phi%'

Best Answer

Related Solutions

MySQL Select Statement – How to Make a Select Statement Get Blocked

Mysql – Select and update specific thesql data in single query

Related Question