insert when null - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

insert when null

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-05-12 : 09:53:57

Dear All,

I am populating some new tables from old tables, after I did some re-structuring to the database.

I have created a table with langID and langCode.

I also created a temptable, with the data being captured from an old table. The fields that are the most relevant at the moment are lid, and fk_langid.

In the old table, lid, has strings in it, for example en, fr, bg etc, and fk_langId will be the foreign key to the new table.

Now my problem is that there are some lid's which do not exist in the new table, and as so I need to hardcode an id for them in the new table.

So I was doing something like this:-

update #tempTable
SET fk_langid =
(
SELECT lid,
CASE
WHEN lid like '%sr%' THEN '1234'
END
FROM #tempTable
)

But I am getting an error:-

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can you help me out on how I can update the fk_langid in the temptable

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-12 : 09:56:42

Try this
update #tempTable
SET fk_langid =
(
SELECT lid=CASE
WHEN lid like '%sr%' THEN '1234'
END
FROM #tempTable
)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-12 : 09:57:18

[code]update #tempTable
SET fk_langid = CASE
WHEN lid like '%sr%' THEN '1234' else lid
END[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-05-12 : 10:00:07

yeps got that
was going to post infact

update #tempTable
SET fk_langid =
(
--SELECT lid,
CASE
WHEN lid like '%sr%' THEN '1234'
END
--FROM #tempTable
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 10:36:26

quote:
Originally posted by monfu

yeps got that
was going to post infact

update #tempTable
SET fk_langid =
(
--SELECT lid,
CASE
WHEN lid like '%sr%' THEN '1234'
END
--FROM #tempTable
)

Dont need the braces too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources