delete sql rows

[Castillo]

hey there, im trying to delete rows from a table in the sqlite system, the problem is that i want to check all accounts that has less 100 deaths then delete all those rows but the problem is that it deletes all rows not just the ones that has less than 100 deaths, here is my code. if someone could help me i will really appreciate,

function delRows ( thePlayer )
local accountTable = getAccounts ()
if #accountTable == 0 then
outputChatBox( "There are no accounts.", thePlayer )
else
for k,accounts in ipairs (accountTable) do
local re = executeSQLQuery("SELECT deaths FROM playerstbl WHERE accountname = '"..getAccountName(accounts).."'")
if ( re ~= 100 ) then
executeSQLDelete("playerstbl", "deaths")
outputChatBox( "There are " .. #accountTable .. " with that data!", thePlayer )
end
end
end
end
addCommandHandler( "acc", delRows )

[dzek (varez)]

less than 100 deaths

if ( re ~= 100 ) then

WTF??!! you are checking if this is NOT 100, any other value = good (true) value!!

re<100

is this what you need?

can't you use?

DELETE FROM table_name WHERE deaths<100

[Castillo]

less than 100 deaths

if ( re ~= 100 ) then

WTF??!! you are checking if this is NOT 100, any other value = good (true) value!!

re<100

is this what you need?

can't you use?

DELETE FROM table_name WHERE deaths<100

huh? i dont understand , if i remplace "if ( re ~= 100 ) then" with "if ( re re<100 ) then" just says

"attempt to compare table with number"

[50p]

less than 100 deaths

if ( re ~= 100 ) then

WTF??!! you are checking if this is NOT 100, any other value = good (true) value!!

re<100

is this what you need?

can't you use?

DELETE FROM table_name WHERE deaths<100

huh? i dont understand , if i remplace "if ( re ~= 100 ) then" with "if ( re<100 ) then" just says

"attempt to compare table with number"

Obviously, because result is a table not a number

[dzek (varez)]

re['deaths']<100

[50p]

varez, you're wrong again. Check what executeSQLQuery returns.

SolidSnake14, there is no need to select the rows from table to delete them. 2nd parameter for executeSQLDelete is condition. If you put just "deaths" in there, you will delete all the rows.

executeSQLDelete( "playerstbl", "deaths < 100" );
-- OR
executeSQLQuery( "DELETE FROM playerstbl WHERE deaths < 100" );

Either way, both of these do the same thing. Learn more about SQL queries and you won't even need to use any SQLite functions but executeSQLQuery.

Edited July 13, 2010 by Guest

[dzek (varez)]

oh right...

first i didnt look at the code, i just saw "not equals" comparsion, and wrote DELETE syntax..

and then..

im always forgetting that in sqlite there is always full table with rows, im used to mysql..

but you made a lil' mistake too you gave 2 examples - in first you are checking if deaths>100, in 2nd - less than 100

[Castillo]

varez, you're wrong again. Check what executeSQLQuery returns.

SolidSnake14, there is no need to select the rows from table to delete them. 2nd parameter for executeSQLDelete is condition. If you put just "deaths" in there, you will delete all the rows.

executeSQLDelete( "playerstbl", "deaths < 100" );
-- OR
executeSQLQuery( "DELETE FROM playerstbl WHERE deaths < 100" );

Either way, both of these do the same thing. Learn more about SQL queries and you won't even need to use any SQLite functions but executeSQLQuery.

oh now works, thank you very much.