help with inner join in update statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with inner join in update statement

Author

Topic

smithani
Starting Member

42 Posts

Posted - 2007-08-27 : 14:55:06

Here is my update statements which doesn't work, can you show me an example or provide a hint.

thanks

update property
inner join propinv on propinvid=propertyinvid
set property.lotsize='100'
where property.lotsize <> '' and property.lotize is not null

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 14:56:42

[code]update x
set x.lotsize = '100'
FROM property AS x
inner join propinv on <tablename-or alias-here>.propinvid = <tablename-or alias-here>.propertyinvid
where property.lotsize > ''[/code]

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-27 : 14:58:19

[code]
update P
set P.lotsize='100'
From Property P
inner join propinv PV on PV.propinvid = P.propertyinvid
where P.lotsize <> '' and P.lotize is not null
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

smithani
Starting Member

42 Posts

Posted - 2007-08-27 : 15:07:31

guys thanks for your input, but I had to change my sql a little bit and still get the same error.

update P
set P.lotsize=propinv.lotsize
From Property P
inner join propinv PV on PV.propinvid = P.propertyinvid
where P.lotsize <> '' and P.lotize is not null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 15:09:28

Which error do you get now?

E 12°55'05.25"
N 56°04'39.16"

smithani
Starting Member

42 Posts

Posted - 2007-08-27 : 15:10:30

Incorrect syntax near the keyword 'update'.

smithani
Starting Member

42 Posts

Posted - 2007-08-27 : 15:12:27