UPDATE SQL caqnnot work out the syntax

[mdav2]

I am trying to create an sql statement that updates a field in table A with a value from table B where the key of the two is equal. Lets say the struture is as follows

Table A Table B
=============== ===============
ID (N) ID (N)
Description (C) Description (C)

data may look like thise

Table A Table B
================== ===================
ID Description ID Description
1 HGT 1 empty
2 ABC 2 empty
3 LLK 3 empty
4 PIY 4 empty

When I run the query the data in table should be the same as A where the ID fields match.

I thought it should go something like (ignore space in name):

UPDATE tableb SET description = (SELECT description FROM tablea) where a.id = b.id

The mosy annoting thing about this is I have done something like this before and for the love of god have forgotten how and where I did it. I remember something about making the subquery and alias so it can do the when but I can't remember how. Any help would be greatfully accepted.

Mark Davies
Warwickshire County Council

 

[mdav2]

I have had our DBA look up this on the oracle website and it gives the solution as follows:

UPDATE tableA;
	SET desc = ;
	(SELECT SUBSTR(tableb.desc,2,36) ;
	FROM tableb ;
	WHERE ALLTRIM(tableb.id) = ALLTRIM(tableA.id)) ;
WHERE EXISTS ;
	(SELECT * ;
	FROM tableb;
	WHERE ALLTRIM(tableb.id) = ALLTRIM(tableA.REFERENCE))

however it still errors returning "function name missing )" running parts of the statement all run OK separately (first sub query returns no records but thats becuase it can't becuase of the link.

This is the actual code I am using:

UPDATE dvla_imp_exp ;
	SET dvlaid = ;
	( ;
	SELECT warwickshire.driverid ;
	FROM warwickshire ;
	WHERE dvla_imp_exp.REFERENCE = warwickshire.REFERENCE ;
	) ;
WHERE EXISTS ;
	( ;
	SELECT * ;
	FROM warwickshire ;
	WHERE dvla_imp_exp.REFERENCE = warwickshire.REFERENCE ;
	)

Mark Davies
Warwickshire County Council

 

[TamarGranor]

If you're doing this in VFP 9, you can do this:

UPDATE dvla_imp_exp ;
    SET dvlaid = SELECT warwickshire.driverid ;
    FROM warwickshire ;
    WHERE dvla_imp_exp.REFERENCE = warwickshire.REFERENCE

Tamar

 

[mdav2]

Nope doing it in VFP 8, should have added that to the post. Web research tends to point to work around but can't find any decent examples that I can apply the work. Will keep digging and post something back.

Doing manual updates at the moment and thats not good, even though it's on around 100.

Mark Davies
Warwickshire County Council

 

[TamarGranor]

Does it have to be SQL. You could SET RELATION and use REPLACE. The other thing you could do in VFP 8 is a hyrbrid, using the LOOKUP() function in the SET clause of UPDATE.

Tamar

 

[bborissov]

Tamar,
In VFP9 it is easier to JOIN tables, instead of running query for each record. I think that is faster, but never made a proper tests, so maybe I am not right at all.

UPDATE dvla_imp_exp ;
    SET dvlaid = warwickshire.driverid;
    FROM dvla_imp_exp;
    INNER JOIN dvla_imp_exp.REFERENCE =;
               warwickshire.REFERENCE

Mark,
with VFP8 you should use Tamar's suggestions, RELATION and REPLACE or LOOKUP() with some IIF() in it.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[TamarGranor]

Yeah, I know you can use JOIN, but I'm told it's non-standard SQL. I can't remember whether I've tested performance, but it shouldn't make a difference--the engine should execute the two the same way.

Tamar

 

[Imaginecorp]

Mdav2: what is wrong with a simple:
UPDATE cust_test ;
SET cust_test.custno = customer.custno ;
FROM customer ;
WHERE cust_test.ordinal = customer.ordinal
**** customer has 10,000 and cust_test has 310. updated in ****a flash, performance is not impacted.
Somehow I do not see the reason for the "Select" or the "Inner Join"? If the customer.ordinal is not found in cust_test, cust_test.custno is not updated.... maybe I am missing something.

 

[mdav2]

All the select statements just didn't work. I went with update lookup function which seems to work quite well (thanks Tamar). Speed wise its OK too because were only looking at around 5000 records to search.

For reference in version 8 could not get any of the sql statements in the post to work. Kept coming back with a "command contains unrecognised phrase/keyword". I've double checked the syntax, even opened the tables and used the intellisense to select the fields but still no joy. I didn't want to write a program to do a SCAN REPLACE because SQL should be more efficient.

Mark Davies
Warwickshire County Council

 

[TamarGranor]

The FROM clause in UPDATE is new in VFP 9.

Tamar