SELECT SQL - Updating a field in a table

[AndrewMozley]

I have Table1 which has two fields, cRef_t1 and nValue_t1. cRef_t1 is populated, and there is also Table2 which has the values I want. Each record in Table2 which has a particular reference, cRef_t2, has a corresponding value nValue_t2.

I know that I can put the values into Table1 with code something like:

SELECT Table1
SCAN
SELECT Table2
SEEK Table1.cRef_t1
SELECT Table1
REPLACE nValue_t1 WITH Table2.nValue_t2
ENDSCAN

However this can perhaps be done (updating all the records in Table1) with something like this :

SELECT nValue FROM Table2 a, Table2 b WHERE a.cRef_t1 = b.cRef_t2
. . .

But I have run out of steam there! Is there an SQL statement which can update a table with values from another table?

Thanks. Andrew M.

 

[Dan Freeman]

Is there an SQL statement which can update a table with values from another table?

Yes. Surprisingly, it's the UPDATE - SQL Command.

 

[EzLogic]

Update Table1 set nValue_t1 = Table2.nValue_t2 from Table1 left outer join Table2 on Table1.cRef_t1 = Table2.cRef_t2

Not tested

Ez Logic
Michigan

 

[Mike Lewis]

Andrew, what you need is called a correlated update:

UPDATE Table1 ;
  SET nValue_T1 = Table2.nValue_t2 ; 
  WHERE Table1.cRef_t1 = Table1.cRef_t2

You could also do it with a join as EZ suggested, but I don't think that's essential.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

>... but I don't think that's essential.
of course it's essential, in your case you'd replace many Table records nValue_T1 field with all the same Table2.nValue_t2 because of two errors:
1. You don't join Table2
2. Your where clause is wrong, not including Table2.

Bye, Olaf.

 

[Mike Lewis]

Olaf, that was a simple typing error on my part. Of course, I meant Table2.cRef_t2, not Table1.cRef_t2.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike, but if you only change that you only update one match of ref values. If Table2 is not in the FROM clause, neither direct nor joined, Table2 is just used as alias and the current record is read from it.

EZLogics update query is what's needed.

Bye, Olaf.

 

[Mike Lewis]

OK, I see what you mean, Olaf. I should have read the original question more carefully. He wants to update every record with the value from the record with the matching cRef - not just those where the cRef is is the same.

Andrew, EZLogic's solution is the right one for you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

Thanks very much Ez (and later, Mike). That is clear and helpful.

May I ask a related question. Can I read a value from a table into a local variable, using an SQL statement?

The way that I know goes :

LOCAL lnValue
SELECT Table1
LOCATE FOR cRef_t1 = “ThisRef”
lnValue = Table1.nValue_t1

And I know that I can use SELECT-SQL to read the value into a (one-record, one field) cursor or a one-row array.

SELECT nValue_t1 FROM table1 INTO CURSOR cAnswer
or
DIMENSION aValue
SELECT nValue_t1 FROM table1 WHERE cRef_t1 = "ThisRef" INTO ARRAY aValue

How do I read a value from a table into a local or public variable using an SQL statement? Thanks

 

[Olaf Doschke]

SQL Statements have the destinations you already mentioned: table, cursor (indeed also a table), array. No more, no less. So the best thing matching your need is to query INTO ARRAY aValue and set lnValue = aValue[1,1] or simply use aValue, which adresses element 1 anyway.

Bye, Olaf.

 

[Dan Freeman]

You can't create a variable from a query because the result of a query is always a set. It may be a set of one, but it's still a set. It has the potential to have more than one row, so the only available targets are multi-row capable.

 

[Olaf Doschke]

Forgot one thing, you can also query TO a "display"destination, which includes a file, printer or screen/window, but again no variable.

You better get used to querying INTO CURSOR, as that is what is working universally with views (including remote views), sql-queries, cursoradapters and SQLEXEC.

You can go on computing with a value in alias.fieldname instead of a variable, you can bind controls to a field, you can pass on the value to functions/procedures/methods from the field without going through some variable. Addressing a field with the field name only is prioritized over addressing a memory variable, unless you make use of m.

Bye, Olaf.

 

[Mike Lewis]

If you really wanted to, I suppose you could conceivably write a generic function that always returns the single value from a SELECT. Something like this:

SELECT .... INTO CURSOR csrSingle
luValue = SingleValue()

.....

DEFINE FUNCTION SingleValue
  lcField = EVALUATE("FIELD(1, 'csrSingle')")
  RETURN csrSingle.&lcField
ENDFUNC

I wouldn't do this myself, mainly because things would get mucked up if you happen to have another cursor called csrSingle in the application. (Although you could get round that by not hard-coding the cursor name within the function, but rely on the fact that the cursor will be open in the current work area after the SELECT.) But if you really want a convenient way of returning a single value, it might be worth considering.

I quite like the T-SQL way of doing it:

SELECT @MyVariable = CustID FROM Customer WHERE ....

but that has the disdavantage of being non-ANSI standard.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, that could be shorter

luValue = EVALUATE("FIELD(1)")

But with INTO ARRAY (by the way it doesn't need the DIMENSION in front) you can also address array element 1 by the array name only, so you have the same syntax as if querying into a variable, it will just be a single row, single column array (unless _TALLY is 0 and no record is created, but you typically know whether to check this like you know whether to check FOUND or not).

Bye, Olaf.

 

[Mike Lewis]

I agree. I always use INTO ARRAY myself in these cases. I just thought I'd throw open another possibility in case it appealed to Andrew.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

it MUST be even shorter to really work...

luValue = EVALUATE(FIELD(1))

Bye, Olaf.

 

[SBTBILL]

Have you tried something like this

Insert in table x
(field1,
field2)
values
table2)

I've done this in SQL a lot but the tables have to match.