Update Table from Another Table

[scowans]

Hello All,
I have 2 sql tables (let's call them TableA & TableB). I'm trying to code an x-basic script to an action button so that a comon field between the 2 tables (PO_Status) will be updated from TableA into TableB based on another common field between the 2 tables (EEID). I've been spinning my wheels over the last couple of days trying to figure this out. I reviewed the following article which was very useful: http://www.downloads.alphasoftware.c...SQLTables.html. I tested the following Update Statements and none of them are working properly (rows affected in result is 0):

"UPDATE TableB SET TableB.PO_Status = TableA.PO_Status WHERE TableB.EEID = TableA.EEID"

"MERGE INTO TableB USING TableA ON TableB.EEID = TableA.EEID WHEN MATCHED THEN UPDATE SET PO_Status = TableA.PO_Status"

"UPDATE TableB SET PO_Status = (SELECT TableA.PO_Status FROM TableA WHERE TableA.EEID = TableB.EEID) WHERE TableB.EEID = (SELECT TableA.EEID FROM TableA.EEID WHERE TableB.EEID = TableA.EEID)"

I have been replacing these in the "Update Statement" of the SqlCommand = Line in the code below to no avail.

function serverside_d88d3bd629fb47e89d34ebb1a18dc54c as c (e as p)
'connect to the SQL database
dim cn as sql::connection
flag = cn.open("::Name::HRBase")
if flag = .f. then
serverside_d88d3bd629fb47e89d34ebb1a18dc54c = "alert('Could not connect to database.');"
exit function
end if

'execute command
dim sqlCommand as c
SqlCommand = "Update Statement"

'show confirmation of rows updated
dim rowsUpdated as n
rowsUpdated=cn.CallResult.rowsAffected
cn.Close()

serverside_d88d3bd629fb47e89d34ebb1a18dc54c = "alert('Post Operation Complete. Records Updated: " +rowsUpdated + ". Refresh browser to see updates.');"

end function

Any Tips? Thanks

[bavism]

Re: Update Table from Another Table

My limited SQL knowledge is based on MS SQL, but I suspect the same principles hold true for MySQL.

If I'm understanding the above statements it appears what you are trying to do in your "WHERE" section should be done with a JOIN. The where clause should be used to filter. When you click this button is it's intention to only update cases with a specific EEID? If so you can use the Where to limit WHERE TableB.EEID = '9' (or whatever EEID you are updating). Otherwise if this is to run and possibly update your entire database, then leave out the Where clause.

Both tables already have matching values in the EEID columns, correct?

Assuming your rows in both tables already have matching EEID values and EEID of the records I want to update is 200, I would do this

insert into tableB
(PO_Status)
SELECT tableA.PO_Status
FROM tableA tableA
INNER JOIN tableB tableB
ON tableA.EEID = tableB.EEID
WHERE tableA.EEID = 200


Hope that helps.

Matt

[scowans]

Re: Update Table from Another Table

Thanks Matt for the quick response. When I click the button, the intention is to update the entire table. TableA is attached to a grid w/ an import button to upload excel files (typically anywhere from a couple hundred to a couple thousand records). The PO_Status would then be updated to TableB which has 100,000+ records. I tried using the above syntax adding the inner join and removing the where clause but still no luck. I'm also using MS SQL and I'm wondering if I need to enable portable sql by adding cn.PortableSQLEnabled = .t. into the statement?

[scowans]

Re: Update Table from Another Table

Well.. I was able to get it to work using the below code. Now I'm wondering how this might look using arguments to defend against sql injection attacks? Thanks again!

function UpdateStatement as c (e as p)
'connect to the SQL database
dim cn as sql::connection
flag = cn.open("::Name::HRBase")
if flag = .f. then
UpdateStatement = "alert('Could not connect to database.');"
exit function
end if

'execute command
dim sqlCommand as c
sqlCommand = "UPDATE TableB SET PO_Status = TableA.PO_Status FROM TableB JOIN TableA ON TableB.EEID = TableA.EEID"
flag = cn.execute(sqlCommand)

'show confirmation of rows updated
dim rowsUpdated as n
rowsUpdated=cn.CallResult.rowsAffected
cn.Close()

seUpdateStatement = "alert('Post Operation Complete. Records Updated: " +rowsUpdated + ". Refresh browser to see updates.');"

end function

[GGandhi]

Re: Update Table from Another Table

update statement:
i think the update statement should be
sqlCommand = "UPDATE tableA, tableB SET tableB.PO_Status = tableA.PO_Status WHERE tableA.EEID = tableB.EEID"

question:
it appears that there is no input from the user except for clicking a button.
the tables are there and you are just copying the fields from one to another. it is not clear to me where the sql injection will come from, unless the table itself is corrupted.

also:

function serverside_d88d3bd629fb47e89d34ebb1a18dc54c as c (e as p)

i am not sure how you got this. usually the buttons calls for an ajax call back the defines xbasic function the does not have this suffix.

[scowans]

Re: Update Table from Another Table

Thanks Gandhi for your comments. I tested sqlCommand = "UPDATE tableA, tableB SET tableB.PO_Status = tableA.PO_Status WHERE tableA.EEID = tableB.EEID" and i couldn't get it to work. Fortunately I found a solution above. I'm guessing serverside_d88d3bd629fb47e89d34ebb1a18dc54c is the default serverside function name in Event Handler dialogue window. I always keep this the same b/c when I attempt to rename the function i get an error when i run the grid stating that the default name is missing. I'm also not entirely sure how sql injection works... just been reading that using arguments is the best practice when using sql commands. Unfortunately, as you can probably tell, a lot of this is still relatively new for me

[GGandhi]

Re: Update Table from Another Table

i am glad you have found solution for your problem.
just wanted to show you that the update statement works as i suggested and the ajax callback does not have the suffix.

i am posting a screencast.
i am using mysql, the update statement may be different in sql you are using. that might explain why you had problem.
as long as you are happy with the result, that is all it counts.

please check this out.
http://www.screencast.com/t/yCFovBCBG

[scowans]

Re: Update Table from Another Table

Thanks gandhi!! This video will be very helpful.