Problem with subquery - 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 2008 Forums

Transact-SQL (2008)

Problem with subquery

Author

Topic

Standos424
Starting Member

3 Posts

Posted - 2010-04-05 : 11:13:07

Hi,

i need help with query. I have two tables SKz and SKPPpol. I need update record in SKz from SKPPpol, but in SKPPpol i have to find last record. When i use this query in SQL server, query execute with error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can you help me?

QUERY:
UPDATE SKz SET ProdejDPH=SKPPpol.KcJedn
FROM SKz LEFT JOIN SKPPpol ON SKz.ID=SKPPpol.RefSKz
WHERE SKz.ID IN
(select max(datcreate), RefSKz
from SKPPpol
where RefSKz in
(select RefSKz from SKPPpol
group by RefSKz
having count(*)>=2)
group by RefSKz)

thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 11:20:38

what do you mean by last record? can you show a data sample from tables and explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-05 : 11:34:50

In this case the example code shows that datcreate should give the last record for each identical RefSKz.
So you can try this:


UPDATE SKz 
  SET ProdejDPH=dt.KcJedn
FROM SKz
JOIN
(
SELECT 
  RefSKz, 
  ROW_NUMBER() OVER (PARTITION BY RefSKz ORDER BY datcreate DESC) as rownum,
  KcJedn
FROM SKPPpol
) AS dt
ON SKz.ID=dt.RefSKz AND dt.rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

Standos424
Starting Member

3 Posts

Posted - 2010-04-06 : 10:14:02

it´s great...thx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-06 : 10:34:51

welcome