How to Update fields in a record from other fields in the same record

[Kevin2343]

I have a table similar to this:

Id     Number    Street      Address
---    ------    ------      -----------
100    NULL      NULL        234 Main St
101    NULL      NULL        516 W Oak St
 

I want the end result to look like this:

Id     Number    Street      Address
---    ------    ------      -----------
100    234       Main St     234 Main St
101    516       W Oak St    516 W Oak St
 

How can I do this in SQL? I understand I could do it with scripting but would prefer a SQL statement.

Thanks!
K

[viktorka]

Where do you take the Number and Street values from?
Are they from another table? If yes, you may use INNER JOIN

[Jerry Winston]

Try this out:

DECLARE @t as TABLE(StreetNumber int, StreetName  char(15), rData varchar(20))
 
INSERT INTO @t VALUES( NULL,NULL,'123 main st')
INSERT INTO @t VALUES( NULL,NULL,'421 Cherry ln')
INSERT INTO @t VALUES( NULL,NULL,'544 Peach st')
 
Update @t
    SET StreetNumber = convert(INT,left(rData,charindex(' ',rData)-1)),
    StreetName = right(rData,len(rData)-charindex(' ',rData))
 
SELECT * FROM @t
 

[Kevin2343]

@b0010100
Thanks b0010100! That does the trick.