Can someone help me out with a simple SQL script to update a table ?

You can ? Great ! Okay here's the details . . . .

I need to update the field called Box_Contents in a table called boxes051001 with the data held in a field of the same name in a table called boxes403010 where the field barcode matches in both tables.

Probably really simple, but its Friday

 

just a guess but along the lines of:

dim var1, var2, var3, var4

var1 = boxes051001.Box_Contents
var2 = boxes403010.Box_Contents
var3 = boxes051001.barcode
var4 = boxes403010.barcode

update var1 values (var2) where var3 = var4

Just a guess as I am still learning this stuff - Fosters/Chiark are your men


Box_Contents in a table called boxes051001 with the data held in a field of the same name in a table called boxes403010 where the field barcode matches in both tables.


[Edited by Dream Weaver - 8/23/2002 12:16:46 PM]

 

ps couldn't be arsed writing the acutal syntax out

 

In theory thats exactly what I'm after But it's the syntax that I'm stuck on

 

UPDATE boxes051001
SET Box_Contents = boxes403010.Box_Contents
WHERE
boxes051001.barcode = boxes403010.barcode

I think???

Gary

 

try this...

update boxes051001 set boxes051001.box_contents = boxes403010.box_contents from boxes051001,boxes403010 where boxes051001.barcode = boxes403010.barcode

Difficult to test here without the tables

 

update boxes051001 set Box_Contents =
(select Box_Contents from boxes403010
where boxes403010.barcode = boxes051001.barcode)

I think (This is coming from an Oracle point of view, but think it would work even if it's SQL Server you're after)

And remember to commit!

 

Just tried my script on 2 tables I created and it works.

 

Sorry, forgot to mention its Oracle 8i

Will give these a go. Thanks for your help

 

Gold stars all round