Solved

MySQL UPDATE using a *dynamic* list of values

Posted on 2013-12-22
2
864 Views
Last Modified: 2013-12-22
I need to create a stored proc that accepts a list of one or more id's (example "1,12,13,98") then uses those ids' in a simple UPDATE. Obviously this sql wouldn't work, but it demonstrates the idea:

    
       CREATE PROCEDURE MyProc ( listOfIDs VARCHAR(...) )  
       AS   
       BEGIN
                UPDATE SomeTable
                SET    SomeFlag = 1
                WHERE  IDColumn IN ( listOfIDs )
       END
      

Open in new window


I already know I can use dynamic SQL for this, but ... I'd like to avoid that if possible. Are there any other options for MySQL?

For example, in SQL Server I could use xml functions. Just pass in the id's as an xml string the use a regular JOIN like below. Does mySQL have anything similar?

      DECLARE @str XML
      SET @str = '<id>1</id><id>12</id>....'
       

      ; WITH t AS (
            SELECT ParamValues.id.value('.', 'INTEGER') AS ID
            FROM   @str.nodes('//id') ParamValues(id)
      )
      SELECT *
      FROM   other Table ot INNER JOIN t ON t.id = ot.id

Open in new window

0
Comment
Question by:_agx_
2 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 39735277
Try this, passing it a comma delimited list of id's in a string:
    
       CREATE PROCEDURE MyProc ( listOfIDs TEXT)  
       AS   
       BEGIN
                UPDATE SomeTable
                SET    SomeFlag = 1
                WHERE  FIND_IN_SET(IDColumn, listOfIDs) > 0
       END
      
                                  

Open in new window

Documentation here:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Disclaimer: I haven't tested this, so ensure you have a backup of your data.
0
 
LVL 52

Author Comment

by:_agx_
ID: 39735284
Thanks, I'd completely forgotten about that function!  I can confirm it works.

I also found another (more complex) method using a lookup table. Now to compare the query plans :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Many to one in one row 2 35
Can I skip a node in XML? 9 29
SQL eating up memory? 16 37
TSQL - How to get output results in row with additional character(comma) 7 12
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question