DJ Dunk

iTrader: (5)

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

Dream Weaver

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]

Dream Weaver

ps couldn't be arsed writing the acutal syntax out

DJ Dunk

iTrader: (5)

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

GaryK

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

I think???

Gary

PiNkEyE69

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

V5

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!

PiNkEyE69

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

DJ Dunk

iTrader: (5)

Sorry, forgot to mention its Oracle 8i

Will give these a go. Thanks for your help

DJ Dunk

iTrader: (5)

Gold stars all round