What is the MS SQL equivalent of this Access query?
I wrote this query in Access that pulls data from another table (County_Lookup) and populates rows in my main WORK table. Here is the Access SQL:
UPDATE [work] INNER JOIN county_lookup ON work.county_code = county_lookup.county_code SET [work].division = county_lookup.division;
I am aware that in MS SQL I need to have the SET right after the UPDATE, but after that I don't really know. It gives me syntax errors specifically on the INNER JOIN command.
So this is as far as I have gotten.
UPDATE [work] SET [work].division = county_lookup.division INNER JOIN county_lookup ON work.county_code = county_lookup.county_code;
UPDATE w SET w.division = c.division from [work] w INNER JOIN county_lookup c ON w.county_code = c.county_code;
See also questions close to this topic
Cohort analysis like Firebase - user activity
I have a table in a Postgresql DB where any access to the app is logged through the user ID and the related date (one log per day). Basically, it has the following structure
user_id | created_at 16 | 2018-02-18 154 | 2018-02-18 81 | 2018-02-18 16 | 2018-02-04 ... | ...
Now, I would like to perform a cohort analysis very similar to the one provided by Firebase, shown here below
So, for every period of one week, I would like to know how many users return to the app in the following weeks.
Unfortunately, I'm new to querying a DB and I don't know how to proceed.
I also tried to look at this previous question but it's not been really helpful, as I probably need a bit more guidance at each step.
Thanks for any help
Load values from a spreadsheet to tables in Sql Server
I have to load (kind of) recipes into a Sql Server database.
I receive recipes in the form of a spreadsheet.
The database format for Recipes is like this:
set @IdParameter = (select top 1 id_parameter from Parameters where pa_code ='Macchina') if (select count(*) from Opera6010.dbo.BatParameters (nolock) where id_batch = @IdBatch and id_parameter = @IdParameter) = 0 begin insert into BatParameters with (rowlock) (id_batch, id_parameter, bp_value) values (@IdBatch, @IdParameter, @Macchina) end else begin update BatParameters with (rowlock) set bp_value = @Macchina where id_batch = @IdBatch and id_parameter = @IdParameter end
First (not smart, in my opinion) solution has been:
- import the spreadsheet as a table, same format
- define a cursor over this table, each "fetch" loads a Recipe ID and a list of values for ingredients.
Do a lot of copy, past and replace work to write code like this:
set @IdIngredient = (select top 1 ingr_id from Ingredients where ingr_desc ='Ingredient1') insert into RecIngredients with (rowlock) (recipe_id, ingr_id, value) values (@IdRecipe, @IdIngredient, @Ingredient1) set @return_status = @@error
I am wondering whether a smarter approach exists to create a loop like:
For each row of the input table be @RecipeId the value for Recipe ID read from cursor
For each Ingredient get column name relative to the value currently read get ingr_id for the Ingredient having ingr_desc like the column name set (recipe_id, ingr_id, value) in table RecIngredients
What do you think?
Thanks in advance.
Delete Category/Tag from posts in wordpress but ignore posts that have the following category
classic wordpress structure
I want to remove term_taxonomy_id = 3 from all posts that have term_taxonomy_id 1424 but exclude the ones that also have term_taxonomy_id 3152.
query that does not work:
DELETE FROM wpdb_term_relationships WHERE term_taxonomy_id = 3 WHERE ID IN ( SELECT object_id FROM wpdb_term_relationships WHERE term_taxonomy_id = 1424 ) AND ID NOT IN ( SELECT object_id FROM wpdb_term_relationships WHERE term_taxonomy_id = 3152 )
what am i doing wrong?
Case Statement overriding other case
In the following case statement, the value '1' is being assigned to those who have a Start Term of September 2019 whereas the value '3' should be assigned
SELECT U.*, L.ID AS Lead_ID, CASE WHEN (L.Age_Change < DATEADD(day,-180, GETDATE())) OR (L.Age_Change IS NULL AND L._Stage IS NOT NULL) AND (L._Stage NOT LIKE '%Not Interested%' AND L._Stage NOT LIKE '%Duplicate%') AND (L.Start_Term NOT LIKE '%September 2019%' AND L.Start_Term NOT LIKE '%September 2020%' AND L.Start_Term NOT LIKE '%September 2021%') THEN '1' WHEN (L._Stage IS NULL AND L.Created_Date < DATEADD(day,-45, GETDATE()) AND (L.Start_Term NOT LIKE '%September 2019%' AND L.Start_Term NOT LIKE '%September 2020%' AND L.Start_Term NOT LIKE '%September 2021%') THEN '2' WHEN (L.Age_Change < DATEADD(day,-180, GETDATE())) OR (L.Age_Change IS NULL AND L._Stage IS NOT NULL) AND (L._Stage NOT LIKE '%Not Interested%' AND L._Stage NOT LIKE '%Duplicate%') AND (L.Start_Term LIKE '%September 2019%' AND L.Start_Term LIKE '%September 2020%' AND L.Start_Term LIKE '%September 2021%') THEN '3' ELSE NULL END AS Case_Logic
Any guidance on the issue would be greatly appreciated
Temp tables, Column name or number of supplied values does not match table definition
Even though this tends to look as a duplicate, I had to post it as I can't seem to spot the error.
I don't know if I am mad or what but I can't seem to spot why there is a mismatch in the number of supplied values.
Here are they:
CREATE TABLE #TIPSTOPE_TS ( TIP INT NULL, SIFVAL VARCHAR(5), GRUPA INT NULL, DATUMOD VARCHAR(15), PASIVNA DECIMAL(15,4) NULL DEFAULT(0), REDOVNA DECIMAL(15,4) NULL DEFAULT(0), ZATEZNA DECIMAL(15,4) NULL DEFAULT(0), STOPA DECIMAL(15,4) NULL DEFAULT(0), DATUMDO VARCHAR(15), KONTO VARCHAR(15), ) INSERT INTO #TIPSTOPE_TS SELECT TS.TIP, TS.SIFVAL, TS.GRUPA, CASE WHEN ISDATE(MAX(TS.DATUMOD)) = 0 THEN '2017.12.31' ELSE MAX(TS.DATUMOD) END AS DATUMOD, CAST (2 AS DECIMAL(10,4)) AS PASIVNA, CAST (1 AS DECIMAL(10,4)) AS REDOVNA, CAST (3 AS DECIMAL(10,4)) AS ZATEZNA, TS.REDOVNA, TS.DATUMDO, TP.M1 AS KONTO FROM TIPSTOPE TS WITH(NOLOCK) JOIN TIPPART TP WITH(NOLOCK) ON TP.TIP = TS.TIP WHERE TS.DATUMOD <= '2017.12.31' GROUP BY TS.TIP,TS.SIFVAL,TS.GRUPA,TP.M1,TS.DATUMDO,TS.REDOVNA CREATE NONCLUSTERED INDEX IX_TIPSTOPE_TS ON #TIPSTOPE_TS (TIP, GRUPA, SIFVAL) INCLUDE (DATUMOD)
And the second one...
CREATE TABLE #UNPVT_TIPSTOPE_TS ( TIP INT NULL, SIFVAL VARCHAR(5) NULL, GRUPA INT NULL, DATUMOD VARCHAR(10) NULL, TIP_KS VARCHAR(15) NULL, KAMATNA_STOPA DECIMAL(15,4) NULL DEFAULT(0), DATUMDO VARCHAR(10) NULL, ) INSERT INTO #UNPVT_TIPSOPE_TS SELECT TIP, SIFVAL, GRUPA, DATUMOD, TIP_KS, KAMATNA_STOPA,DATUMDO FROM ( SELECT TIP, SIFVAL, GRUPA, DATUMOD, ISNULL(REDOVNA,0) AS REDOVNA, ISNULL(PASIVNA,0) AS PASIVNA, ISNULL(ZATEZNA,0) AS ZATEZNA,STOPA,DATUMDO FROM #TIPSTOPE_TS ) P UNPIVOT (KAMATNA_STOPA FOR TIP_KS IN (REDOVNA, PASIVNA, ZATEZNA)) AS UNPVT
The second temp tables is taking data from the first one.
When I try to create the second one error is thrown:
Insert error: Column name or number of supplied values does not match table definition
Copying new rows and certain changes from one SQL DB table to another
I have two databases on different servers. I've created a link between the two and am wanting to insert new data and specific changed data on timed intervals, one-way, with a few caveats (If it was just a straight, one-time copy, I'd have no issue). For example:
Table 1 columns to be copied: ID, H, W, D, Name, WO, RM, QTY, PN.
Table 2 will have the same columns, plus: Stage, SD.
I need to, on timed intervals, copy the data from Table1 to Table2. The ID is unique and it needs to check if any other of the originally copied data changed, and if it did, replace it with the new data as well as add new rows. What it cannot do it replace data in the additional columns in Table2, even if some of the original data has changed.
Table1 ID H W D Name WO RM QTY PN 40101 32 32 12 Prod1 9501 Front 1 23.1 40102 12 12 6 Prod2 9501 Back 19 29.5 40103 18 36 12 Prod3 9502 Side 5 43.3 Table2 ID H W D Name WO RM QTY PN Stage SD 40101 32 32 12 Prod1 9501 Front 1 23.1 2018-02-15 2018-02-22 40102 12 12 6 Prod2 9501 Back 19 29.5 2018-02-16 2018-02-22 40103 18 36 12 Prod3 9502 Side 5 43.3 2018-02-16 2018-02-22
As shown above, Table1 data is copied to Table2 through SQL insert query; there are more columns in Table1 than what is shown, but those are the only columns included. There will be times that the data changes for a row in Table1, possibly just one column or maybe all of them except ID. When copying the data to Table2, it should insert new records, update the columns that have changed (only ones copied from Table1) in existing rows and not others columns in Table2 that may or may not already have data in them, such as the Stage or SD column in Table2.
SQL Query in an 1:N relationship
I have the following database, it's a simple relationship between an user and his receipts:
I need to get as many row as users and so many column as much receipt it have. For example:
If user 1 have 2 receipt I have to obtain:
IdUser Receipt 1 Receipt 2 1 12€ 13€
I tried with an inner joint between User and Receipt tables, but I have as many rows users as receipts they have.
SELECT user.idUser, receipt.value FROM user INNER JOIN receipt ON user.IdUser = receipt.IdUser;
SQL with 3 criteria using Access
I am using Access 2010 and currently have the below SQL which works fine:
DELETE DISTINCTROW tbl_added.* FROM tbl_added INNER JOIN tbl_removed ON (tbl_added.SPECIAL_NEED_TYPE = tbl_removed.SPECIAL_NEED_TYPE) AND (tbl_added.NUM_CUST = tbl_removed.NUM_CUST);
I am trying to add another criteria but getting an error:
DELETE DISTINCTROW tbl_added.* FROM tbl_added INNER JOIN tbl_removed ON (tbl_added.SPECIAL_NEED_TYPE = tbl_removed.SPECIAL_NEED_TYPE) AND (tbl_added.NUM_CUST = tbl_removed.NUM_CUST) AND (tbl_added.ADDED_REMOVAL_DT < tbl_removed.ADDED_REMOVAL_DT) ;
Could not delete from specified tables
The last criteria I have added is a date
No SQL result on inner join
I have two tables:
- failures; with fields FailureId, Cause, Effect, Postalcode, Housenumber
- coordinates; with fields Postalcode, Housenumber, X, Y
The table coordinates contains all the postalcodes and housenumber combinations that are present in the table failures.
I would like to do a query on both tables and get back all fields from the table failures and only the matching X and Y fields from the table coordinates (matching on postalcode and housenumber).
For this I wrote an inner join SQL statement for a query in MS Access like
SELECT failures.*, coordinates.X, coordinates.Y FROM failures INNER JOIN coordinates ON failures.Postalcode = coordinates.Postalcode AND failures.Housenumber = coordinates.Housenumber;
Example data table Failures:
FailureId DateReported Postalcode Housenumber 1 4-1-2018 7711 AH 62 2 20-2-2018 8326 AC 52
Example data table Coordinates:
Id Postalcode Housenumber x y 1 7711 AH 62 216050,15 510800,37 2 7711 AH 64 216046,74 510809,37 3 7711 AH 66 216042,18 510821,34 4 8326 AC 52 196171,06 522611,87
What I would like as a result is:
FailureId DateReported Postalcode Housenumber X Y 1 4-1-2018 7711 AH 64 216046,74 510809,37 2 20-2-2018 8326 AC 52 196171,06 522611,87
My query doesn't return any record though, only the header row with the field names. So what am I doing wrong here?
Thanks for your help!