Hi all. I am using VB.NET with a MySQL database. I want to update this code to do it all in ONE SQL instead of THREE. Anyone know how?

Here's the code I'm using, works fine but too slow with multiple lines...

[code] If count3 = "1" Then

Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim SQL As String
myCommand.Connection = conn
myAdapter.SelectCommand = myCommand
SQL = "UPDATE employees SET emprole1 = '" & val2 & "' WHERE emprole1 = '" & val1 & "'"
myCommand.CommandText = SQL
SQL = "UPDATE employees SET emprole2 = '" & val3 & "' WHERE emprole2 = '" & val2 & "'"
myCommand.CommandText = SQL
SQL = "UPDATE employees SET emprole3 = '" & val4 & "' WHERE emprole3 = '" & val3 & "'"
myCommand.CommandText = SQL

End If[/code]


  • Because you are trying to change values in different columns, based on the values in other various columns, you may find the way you have now is the simplest, however you can use some case statements to test if it's any quicker, something like below.

    UPDATE categories
    SET display_order = CASE id
    WHEN 1 THEN 3
    WHEN 2 THEN 4
    WHEN 3 THEN 5
    title = CASE id
    WHEN 1 THEN 'New Title 1'
    WHEN 2 THEN 'New Title 2'
    WHEN 3 THEN 'New Title 3'
    WHERE id IN (1,2,3)

    Go to this site to understand it, it is where I took above code from:


    HTH, Dai

    Do or do not, there is no try. |
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion