B4J Question Update Else Insert SQL

[aaronk]

Hi,

I am trying to update a record in the MySQL database, however if the record doesn't exists I want to add it.

I can update the record by doing:

Dim item, Account, location As String
        item = "123"
        Account = "ABC"
        location = "Test"
        
        sql.ExecNonQuery2("Update Table SET Item = ? WHERE Account = ? AND Location = ?",Array (item,Account,location))

However, I am not sure if there is a query I can use to detect if the record wasn't updated and the add it ?

Anyone know if there is a way in updating the record if it doesn't update then to insert it ?

 

[DonManfred]

Insert into a MySQL table or update if exists

I want to add a row to a database table, but if a row exists with the same unique key I want to update the row. For example: INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19); Le...

stackoverflow.com

 

[aaronk]

Insert into a MySQL table or update if exists I want to add a row to a database table, but if a row exists with the same unique key I want to update the row. For example, insert into table (id, name, age) values(1, "A", 19) Let’s say the un...

stackoverflow.com

That seems to only work if using a primary key from what I can see.
I am not using a primary key in my database table.

 

[Erel]

MySQL insert on duplicate update for non-PRIMARY key

I am little confused with insert on duplicate update query. I have MySQL table with structure like this: record_id (PRIMARY, UNIQUE) person_id (UNIQUE) some_text some_other_text I want to update

stackoverflow.com

 

[aaronk]

MySQL insert on duplicate update for non-PRIMARY key

I am little confused with insert on duplicate update query. I have MySQL table with structure like this: record_id (PRIMARY, UNIQUE) person_id (UNIQUE) some_text some_other_text I want to update

stackoverflow.com

When I use that I seem to get an error.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'IF ROW_COUNT()=0 INSERT INTO Table (Account, Location, Value) VALUES ('A' at line 1

Maybe I am doing it wrong ?

Dim item, Account, location As String
    item = "123"
    Account = "ABC"
    location = "Test"
       
sql.ExecNonQuery2("UPDATE Table SET Item=? WHERE Location=? AND Account=? IF ROW_COUNT()=0 INSERT INTO Table (Account, Location, Item) VALUES (?,?,?)",Array (item,location,Account,Account,location,item))

 

[Erel]

Just make a query yourself to test if it exists and then add it if not.

 

[aaronk]

I am going to see if I can re-design my SQL database, and include a primary key somehow based on the data I am using. This way I can use the ON DUPLICATE KEY function in the query.

 

[codie01]

This is probably close to what you require!

Dim myItem As String = "123"
Dim myAccount As String  = "ABC"
Dim myLocation As String = "Test"

Dim itemsql1 As SQL
itemsql1.Initialize2("org.mariadb.jdbc.Driver", "jdbc:mariadb://" & ABMShared.myServerAddress & ":3306/" & myServerDatabase, myServerUsername, myServerPassword)

Dim itemCursor1 As ResultSet
itemCursor1 = itemsql1.ExecQuery("SELECT * FROM mytable WHERE Item = '" & myItem & "' AND  account = '" & myAccount & "' AND location = '" & myLocation & "'")

If itemCursor1.Nextrow = True Then
    itemsql1.ExecNonQuery2("Update mytable SET item = ? WHERE location = '" & myLocation & "' AND Account = '" & myAccount & "'",  Array As Object(myItem))
Else
    itemsql1.ExecNonQuery2("INSERT INTO contract VALUES(?,?,?,)", Array As Object(myItem,myAccount,myLocation))
Endif

itemCursor1.Close
itemsql1.close

 

[aaronk]

I ended up joining the account and location fields together and making that a primary key.

Then I can insert it like:

sql.ExecNonQuery2("INSERT INTO Table (Location, Value) VALUES (?,?) ON DUPLICATE KEY UPDATE Value=?",Array (Account & "_" & Location,Value,Value))