update multiple rows with SQL

Question

I have two tables

student:
(student_id, student_name)

and

license:
(license_id, student_id)

And I have a list of student ids (for example "3,4,5")

I want to update 3(list size) rows in table license and change their student_id field from null to 3,4,5 respectively.

How do I do that? Running a loop of updates is not a suitable solution for me. Thanks in advance.

What RDBM you are using? you tag both mysql and oracle.You need to use PL/SQL — rkosegi
– rkosegi, Commented Jun 1, 2012 at 9:11
are there existing rows in license table with null value in student_id. Do you want to insert records in this table or update. — Romil Kumar Jain
– Romil Kumar Jain, Commented Jun 1, 2012 at 9:13
I am using mysql, though we are considering to move for Oracle. — psylocybe
– psylocybe, Commented Jun 1, 2012 at 9:15
there already exists a lot of rows with sutdent_id=null in the 'license' table — psylocybe
– psylocybe, Commented Jun 1, 2012 at 9:16
What is the Primary Key of table licence? Is it licence_id? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 1, 2012 at 10:34

Allan Chua · Accepted Answer · 2012-06-01 09:21:04Z

1

I have an MS SQL version of an id parser. You can use this to generate a table with a single column that contains your ids it takes a parameter of type varchar(max). you can use it on a select statement like

SELECT * FROM TABLENAME
WHERE COLUMNAME IN (SELECT value FROM parseIDs ('1,2,3'))

Here is the function:

CREATE FUNCTION parseIDs  
(
    @IDList VARCHAR(MAX)
)
RETURNS @IDs TABLE (value VARCHAR(80))
AS
BEGIN
    IF @IDList is null
        RETURN

    DECLARE @Len INT, 
            @Pos INT, 
            @Cur INT

    SELECT @Pos = 0, 
           @Len = 0, 
           @Cur = 1, 
           @IDList =  CASE 
                        WHEN SUBSTRING(@IDList, LEN(@IDList), 1) = ',' THEN @IDList 
                        ELSE @IDList + ',' 
                      END

    SELECT @Pos = CHARINDEX( ',', @IDList, @Cur)
    WHILE (@Pos <> 0)
    BEGIN
        INSERT @IDs VALUES (LTRIM(SUBSTRING( @IDList, @Cur, (@Pos - @Cur))))
        SELECT @Cur = @Pos + 1
        SELECT @Pos = CHARINDEX( ',', @IDList, @Cur)
    END
    return
END

answered Jun 1, 2012 at 9:21

Allan Chua

10.3k9 gold badges45 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

psylocybe Over a year ago

Allan, thank you. This would become handy. But it's not the main problem. The problem is update query itself. How would it look like?

Allan Chua Over a year ago

@user1014523 anytime buddy ;).. We always find this function very useful when using SQL server :)

ypercubeᵀᴹ Over a year ago

This can be helpful for SQL-Server but doesn't answer the question. MySQL has FIND_IN_SET() function which does a similar thing.

Collectives™ on Stack Overflow

update multiple rows with SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related