Mysql – Select and update specific thesql data in single query

MySQLselectupdate

I am trying to first select data from a row which contains certain characters. I would then like to update and append new data into another row of those items that were selected. This is my current solution I came up with when trying to combine the two tasks into a single query.

SELECT *
FROM `parts`UPDATE  `parts` SET  
`keywords` = CONCAT(`keywords` ,  'Phillips' )
WHERE INSTR(`name`, 'PHI') > 0

Best Answer

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%'

Related Solutions

Mysql – execute a prepared statement inside a MySQL SELECT

From the question, it looks like what you have written quasi-resembles what Oracle calls an anonymous code block. MySQL does not facilitate such a mechanism.

With regard to what you want to accomplish, you do not need a stored procedure.

Try assembling the SQL statement like this:

@stmt = 'SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs FROM buyerList AS b ';
@stmt = CONCAT(@stmt,'LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass ');
@stmt = CONCAT(@stmt,'LEFT JOIN pricelists p ON ');
SELECT GROUP_CONCAT(CONCAT('(p.sid = a.sid AND p.preisliste = ',sid,' AND p.ean = a.ean AND p.iln = ',pricelist',)') SEPARATOR ' OR ')
INTO @LeftJoinClause FROM preislisten;
@stmt = CONCAT(@stmt,@LeftJoinClause,' WHERE b.bid = ?');
SELECT @stmt\G

This will print out the desired query

If it is the desired query, execute it

PREPARE sql FROM @stmt;
EXECUTE sql using @param_iln;
DEALLOCATE PREPARE sql;

Give it a Try !!!

If you actually want the query to be small without hardcoding every value just write the code with a more straightforward LEFT JOIN setup

SELECT p.sid, ifnull(p.pricelist,"BASE"), count(*) AS recs FROM buyerList AS b
LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
LEFT JOIN pricelists p ON
    (p.sid = a.sid AND p.preisliste = a.sid AND p.ean = a.ean)
WHERE b.bid = ?
GROUP BY p.sid,p.pricelist;

I also just noticed you have a.sid and a.ean, what table has an alias of a ?

Mysql – Help with a complicated MySQL Query inserting data using select from 2 tables

Okay its done, the cursor usage worked

here is the code m using

UPDATE player_list_items SET player_list_item_cellX = cell_X, player_list_item_cellY = cell_Y
             WHERE player_list_id IN (
                 SELECT player_list_id FROM (
                 SELECT player_list_id FROM player_list_items 
                 WHERE player_list_player_id = playerID AND player_list_list_id = @listID AND player_list_item_cellX IS NULL
                 ORDER BY player_list_id LIMIT 1
                 ) tmp
             );

please let me know if this solution is not a good one... as I found nothing besides this to update multiples rows with different values..

its in a loop against cursor that have all the map_details data needs to be put in this table

Best Answer

Related Solutions

Mysql – execute a prepared statement inside a MySQL SELECT

Mysql – Help with a complicated MySQL Query inserting data using select from 2 tables

Related Question