January 14, 2009 at 7:17 pm
Hi i am trying to do update from if clause
if (EXISTS(select name from tablename where name = table2.name and date = table2.date))
begin
update t
set record = table2.record
and so on
where tablename, table2
but i get error like The multi-part identifier "table2.name" could not be bound.
what should i do,
please guide
January 14, 2009 at 7:22 pm
You need to either include Table2 in the FROM clause or use an alias
e.g.
UPDATE T
SET Col1 = Table2.ColumnA
from MyFirstTable T
inner join MySecondtable Table2
ON T.ID = Table2.ID
WHERE etc
January 14, 2009 at 7:33 pm
no no.
I am trying to do update if exist else insert
thanks
January 14, 2009 at 10:26 pm
You're IF EXISTS...SELECT statement is missing the alias name for the table.
Try this:
if (EXISTS(select name from tablename table2 where name = table2.name and date = table2.date))
begin
Or this:
if (EXISTS(select name from tablename where name = name and date = date))
begin
January 16, 2009 at 8:04 am
Did you mean to say you are trying to refer a table mentioned in your UPDATE statement in your SELECT statement? How is that possible?
pat (1/14/2009)
if (EXISTS(select name from tablename where name = table2.name and date = table2.date))
In your SELECT statement, by looking at your joins you are trying to compare values from 2 tables. So, it should be:
if (EXISTS(select name from tablename table1,tablename2 table2 where table1.name = table2.name and table1.date = table2.date))
And, your UPDATE statement should be like
if (EXISTS(select table1.name from tablename1 table1,tablename2 table2 where table1.name = table2.name and table1.date = table2.date))
begin
update tablename1
set record = t2.record,
....
from tablename1 t1
inner join tablename2 t2
on t1.name = t2.name
end
John Rowan (1/14/2009)
Try this:
if (EXISTS(select name from tablename table2 where name = table2.name and date = table2.date))
begin
This won't work since the SELECT statement is querying only one table here, which table should it refer to for name and date?
if (EXISTS(select name from tablename where name = name and date = date))
begin
This won't satisfy the requirement since this will always be TRUE.. it is like WHERE 1=1.
January 16, 2009 at 10:24 am
gyessql is absolutly correct. Thanks for catching my mistake. You cannot reference a table outside of the SQL statement. gyessql's solution will work, and so would something like this:
UPDATE tablename1
SET record = t2.record, ....
FROM tablename1 t1
INNER JOIN tablename2 t2
ON t1.name = t2.name
IF @@ROWCOUNT = 0
INSERT INTO TableName1........
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply