Update Last Record

[Boxwar]

Hi,

I am creating a Python function which contains a SQL string. When the function is loaded, the SQL table must be updated with the values from my program.
This is all done.

Though I'm stuck at the SQL string. How can i say that I want to update the last record ?

I heard about the LAST command, but it doesnt work with my MS SQL Enterprise manager.

Example:
UPDATE Table SET column = 123 WHERE LAST

[Boxwar]

Hi,

I am creating a Python function which contains a SQL string. When the function is loaded, the SQL table must be updated with the values from my program.
This is all done.

Though I'm stuck at the SQL string. How can i say that I want to update the last record ?

I heard about the LAST command, but it doesnt work with my MS SQL Enterprise manager.

Example:
UPDATE Table SET column = 123 WHERE LAST

I forgot to say. There is an error with python which does not let me use INSERT INTO since there can only be updated 8 values at once

[iburyak]

You should have primary key or date in your table then you can update maximum record using a key or a date.

To help you with insert you have to show me what columns you have in a table, datatypes and nullable and insert statement you used.

Thank you.

Irina.

[Boxwar]

Hi Irina,

Thanks for the reply. I have a primary key. My database is ordered by this numeric primary key.
I don't really need any help on Insert, but i suppose i could show the string anyway. Python or SQL doesn't allow me to insert more than 8 values at once.

sqlStr = """INSERT INTO Pforte (EIN_GEW, KARTEN_NR, AUFLIEGER, SPEDITION, LIEF_NR, LIEFS_GEW, PROD_NR, rezept) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')""" %(Gewicht, KartenNR, KFZ, Spedition, LieferscheinNr, LieferscheinGewicht, ProduktNr, RezeptNr)

I know how to select the last record, but not how to modify it.

selecting:
SELECT TOP 1 *
FROM Pforte
ORDER BY LAUFENDE DESC

Please help

[iburyak]

Did you mean you are trying to execute statement like this and it doesn't work?


[PHP]
sqlStr = "INSERT INTO Pforte (EIN_GEW, KARTEN_NR, AUFLIEGER, SPEDITION, LIEF_NR, LIEFS_GEW, PROD_NR, rezept, Gewicht, KartenNR, KFZ, Spedition, LieferscheinNr, LieferscheinGewicht, ProduktNr, RezeptNr) "
sqlStr = sqlStr & "VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s' ,'%s','%s','%s','%s','%s','%s')" [/PHP]

I removed some double quotes which I am not sure were for.
If it is not a full list of columns in a table the rest of columns must allow nulls in them. Otherwise insert will not be allowed.
Show me error message you get while executing this statement in SQL:

[PHP]
INSERT INTO Pforte (EIN_GEW, KARTEN_NR, AUFLIEGER, SPEDITION, LIEF_NR, LIEFS_GEW, PROD_NR, rezept, Gewicht, KartenNR, KFZ, Spedition, LieferscheinNr, LieferscheinGewicht, ProduktNr, RezeptNr) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s' ,'%s','%s','%s','%s','%s','%s')[/PHP]

Are there all columns listed here allow character strings?
Do you have rights to all other columns?

[Boxwar]

hmmm. I am not sure how I got it, but the Insert string seems to work now. It can insert all values at once now. But this was a Python problem and Lol! it was no PHP code. Anyway, the insert string is fine now.

But I still need a code which can update the last record of the database. My python function will submit the data and Inserts it into the database, but later more data has to be submitted and updated in the same record.
INSERT INTO will just add a record to the database but my script doesn't know the primary key number, so i cant refer to a record in my script.
How can I do this ?

[iburyak]

If you used statement I provided then I did correct some things that were not correct.

Use following Update statement to update last record:
[PHP]
Update Pforte Set
column_name1 = value,
column_name2 = value
where LAUFENDE = (SELECT TOP 1 LAUFENDE
FROM Pforte
ORDER BY LAUFENDE DESC)[/PHP]

You can write an SQL stored procedure that returns last inserted primary key as an output parameter. This way it would be for sure last inserted record.
In just update last record it could happen that it is not last record that you just inserted.

Good Luck.

[Boxwar]

If you used statement I provided then I did correct some things that were not correct.

Use following Update statement to update last record:
[PHP]
Update Pforte Set
column_name1 = value,
column_name2 = value
where LAUFENDE = (SELECT TOP 1 LAUFENDE
FROM Pforte
ORDER BY LAUFENDE DESC)[/PHP]

You can write an SQL stored procedure that returns last inserted primary key as an output parameter. This way it would be for sure last inserted record.
In just update last record it could happen that it is not last record that you just inserted.

Good Luck.

Wow! It worked. Thank you very much for the help.

Simon