MySQL Loop & Update

[Peter.Greulich]

There's got to be a better way to update records in a loop. As I understand it, the only way to update (not insert) a loop is to fetch through the result set and then run a SQL update command for each iteration on some unique criteria of the record you are fetching. This is especially problematic if the table doesn't have a unique key (I know it should, but never mind that). You would thing that rs.nextRow() would have a built in counter (somehow) or a method to address that row directly w/o doing a where clause for each single record (130K or more each day).

Any suggestions?

[frankbicknell]

Re: MySQL Loop & Update

I'm not sure of exactly what you are doing. I used to do updates the way you described but after I discovered procedures I was able to just call a procedure and let mysql do the work. I think you could do the same with sql

[Steve Wood]

Re: MySQL Loop & Update

Peter, can you give an example of what you want to update? You can update the whole table with something as simple as:

UPDATE mytable SET field1 = 'abc'

or

UPDATE myable SET field1 = (field1 * .10) + field2

[Peter.Greulich]

Re: MySQL Loop & Update

Thanks guys. Steve, it needs to be a loop. I agree, it would be easy if it were a global update. But each row may change depending upon previous values. We are actually converting a VFP script to xbasic. Below is part 1 of that (VFP) script which we are working on now.

PUBLIC m.bldtype,cpart
m.bldtype='BLD'
cpart=ALLTRIM(PART)
GO TOP

DO WHILE !EOF()
	IF cpart<>PART
		cpart=PART
		m.bldtype='BLD'
	ENDIF
	IF bnote='***********' AND SUBSTR(bpart,1,3)='EAR' AND m.bldtype<>'EAR'
		m.bldtype='EAR'
*!*		ELSE
*!*			m.bldtype=''	
	ENDIF
	IF bnote='***********' AND SUBSTR(bpart,1,3)='KIT' AND m.bldtype<>'KIT'
		m.bldtype='KIT'
*!*		ELSE
*!*			m.bldtype=''	
	ENDIF
	IF bnote='***********' AND m.bldtype='KIT' AND EMPTY(bpart) AND m.bldtype<>'MISC'
		m.bldtype=''
*!*		ELSE
*!*			m.bldtype=''	
	ENDif	

	DO CASE
		CASE m.bldtype='BLD'
			REPLACE bldtype WITH 'BLD'
		CASE m.bldtype='EAR'
			REPLACE bldtype WITH 'EAR'
		CASE m.bldtype='KIT'
			REPLACE bldtype WITH 'KIT'
		CASE m.bldtype='BOX'
			REPLACE bldtype WITH 'BOX'

	ENDCASE
	SKIP
ENDDO

So 100,000+ records will be real slow and this script will be run daily.

[Steve Wood]

Re: MySQL Loop & Update

UPDATE mytable SET field1 = 'abc' IS a loop, it runs through each record in turn; just omit any WHERE statement. If you Google "SQL UPDATE CASE statements" you can find examples that come close to what you have to write.

[Peter.Greulich]

Re: MySQL Loop & Update

I know it's a loop, but as you can see, I can't just set "abc", each row is different. Buy I'll Google your suggestion to see what show.

[Peter.Greulich]

Re: MySQL Loop & Update

Following up on Steve's suggestion, here are two of the most simplest variations I can't get to work in a sample table of two fields:

This one uses MySql syntax:
SQL = "UPDATE Loop SET no = '44' IF item = 'boo'"
conn.execute(SQL)

This uses Alpha's portable SQL syntax:
SQL = "UPDATE Loop SET no = IF(item = 'boo','44','00')"
conn.execute(SQL)


I get this:

1064 - 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Loop SET no = If(item = 'boo','44','00')' at line 1'

Any ideas where the error is?

[Bill Griffin]

Re: MySQL Loop & Update

Peter,

I think you should be using the WHERE clause instead of IF.

SQL = "UPDATE Loop SET no = '44' WHERE item = 'boo'"
conn.execute(SQL)

[Peter.Greulich]

Re: MySQL Loop & Update

Bill,

Thanks, but that's exactly what I am trying to avoid as per my above posts.

[Bill Griffin]

Re: MySQL Loop & Update

Back to what Steve said - google "MYSQL UPDATE CASE Statements" plenty of examples.... - check this out. converting multiple update queries into case statement IF is not going to work as you used it.

[Peter.Greulich]

Re: MySQL Loop & Update

Thanks Bill. Already had been there. But I found out my immediate problem - in Post #7 above, my simple example failed, because "loop" is a reserved word in MySql. So now, at least, I have the Alpha syntax "If" method working. Progress....

[Bill Griffin]

Re: MySQL Loop & Update

Cool. So much (too much) to learn :)

[Peter.Greulich]

Re: MySQL Loop & Update

And native Sql works too as in...

SQL = <<%str%
    UPDATE LoopTest 
    SET no = 
    CASE
    WHEN item = 'alpha' THEN '7'
    WHEN item = 'hello' THEN '99'
    WHEN item = 'yikes' THEN '333'
    END;
%str%

conn.PortableSQLEnabled = .f.
conn.execute(SQL)

More progress...

[Bill Griffin]

Re: MySQL Loop & Update

Pete,

thanks for posting the results. So many unresolved threads, it's nice to seeing the resolution posted.

[Pat Bremkamp]

Re: MySQL Loop & Update

Peter,

It would be faster to build a single statement (using *concat or *concat_lines) and run one update. Use the loop to create a number of CASE statements, and when you get enough, then run one update.

Speaking of updates, I see in your last post that you are already doing this? You should be able to get at least 100 case statements in one update, perhaps more.