Hi, When I run my query below I receive this error msg: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." I am trying to add all values from one table into another: INSERT INTO ATTRIBUTESTRING (AttributeDefinitionID, AttributeValue) Select a.attributedefinitionid, (SELECT * FROM COUNTRY WHERE COUNTRYNAME <> 'Other') from Attributedefinition as a where a.AttributeName = 'Regions'; Can you please advise, how I can solve this problem? Many thanks.
Yes, the way you're using the sub-select from the COUNTRY table, it's returning multiple values, either columns or rows or both, and it can only return a single column and single row when used the way you have it. You need to change that part of the query so that it only returns one value. At the least, this probably means using a column instead of *. But, since that's only ever going to return a single value, meaning it's not joining to the other table in the SELECT statement, why not just run it once before the INSERT and load the value into a variable which you can then use in your INSERT statement: DECLARE @CountryName varchar(50) --or whatever SELECT @CountryName = CountryName FROM Country WHERE CountryName "other' --again this needs to return a single row INSERT INTO.... SELECT a.attributedefinitionid, @CountryName FROM...
Am I right in thinking that the COUNTRY table only has the one field? If so, try rewriting the query as: INSERT INTO ATTRIBUTESTRING (AttributeDefinitionID, AttributeValue) SELECT a.AttributeDefinitionID, c.* FROM AttributeDefinition a, Country c WHERE a.AttributeName = 'Regions' AND c.CountryName 'Other'
Do you want all countries for each AttributeDefinition? If thats the case, try a cross join or Thomas solution. Personally I prefere the Cross Join syntax: INSERT INTO ATTRIBUTESTRING (AttributeDefinitionID, AttributeValue) Select a.attributedefinitionid, c.CountryName from Attributedefinition as a cross join (SELECT CountryName FROM COUNTRY WHERE COUNTRYNAME 'Other') as C where a.AttributeName = 'Regions';