problem in inner join

Hey,

I want to update the price in the items table to be equal to the corrosponding price in the books table but my problem is in the "on stmt", i cant set items.id=books.id because in the items table the id is saved as nvarcharex:b1234 where b identifies a book and in the books table it is saved as int as follows 1234 and this is the only primary key ! so how can i solve it and how can i write the query to update the price in the items table??

Thank you

Hiba

You can try to use some string functions to do so. Fo r example you can try RIGHT(items.id,4)=books.id if both ids' column is nvarchar column. Or

SUBSTRING( items.id, 2,LEN( items.id))=CONVERT(NVARCHAR,books.id)) if the books id is not nvarchar.

|||

Actually you can use RIGHT or SUBSTRING to get you data like RIGHT(book,4) if you are sure that your ID will be no longer then 4 digits or

if you know that you will always have 1 character to identify book you can use syntax like

where items.ID= LEFT(items.ID,1)+books.id

actually it is the same like using

where items.ID= SUBSTRING(items.ID,0,1)+books.id

Because Left just call substring to do its work.

Thanks

JPazgier

|||

hi.

are u familiar with adobc?

how can i use dataadapter using adobc?

my code is:

Dim sqldataadapter As New SqlClient.SqlDataAdapter(stringQuery, MyConn)
Dim ds As New DataSet()
Dim foundrow, temprow As DataRow
Dim ds2 As New DataSet
Dim temp_data_table As New DataTable
sqldataadapter.Fill(ds, "TT0001")

Dim sqldataadapter2 As New SqlClient.SqlDataAdapter(stringQuery2, MyConn)
sqldataadapter2.Fill(ds2, "tempo_db")
Dim date_ctr As Integer

but it doesnt accept sqlClient....what can u suggest?

|||

Hey, Anyways thank you but I sloved it in this way:

update

items

set

items.weight=books.weight,items.profit=books.profit,items.alldiscount=books.alldiscount

from

(booksjoin itemson'b'+cast(books.idAsnvarchar(12))=items.id)

where

items.center='lb'and items.ordernum>47000

Thanx

Hiba